package com.onlineMIS.ORM.DAO.headQ.report;


import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.Date;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.xml.parsers.DocumentBuilder;

import org.apache.catalina.Session;
import org.apache.commons.lang.StringUtils;
import org.apache.naming.java.javaURLContextFactory;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.Criteria;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.onlineMIS.ORM.DAO.Response;
import com.onlineMIS.ORM.DAO.chainS.report.template.HqSalesStatisticsReportDetailTemplate;
import com.onlineMIS.ORM.DAO.chainS.report.template.HqSalesStatisticsReportTemplate;
import com.onlineMIS.ORM.DAO.chainS.report.template.HqTabletReportTemplate;
import com.onlineMIS.ORM.DAO.chainS.user.ChainStoreDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.BrandDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.CategoryDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ColorDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.HeadQTagDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.HeadQTagGroupDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ProductBarcodeDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ProductBarcodePicDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ProductDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.QuarterDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.YearDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.custMgmt.HeadQCustDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.finance.ChainAcctFlowDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.finance.ChainFinanceTraceImpl;
import com.onlineMIS.ORM.DAO.headQ.finance.ExpenseHqTypeImpl;
import com.onlineMIS.ORM.DAO.headQ.finance.FinanceBillImpl;
import com.onlineMIS.ORM.DAO.headQ.finance.FinanceService;
import com.onlineMIS.ORM.DAO.headQ.finance.HeadQAcctFlowDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.inventory.InventoryOrderDAOImpl;
import com.onlineMIS.ORM.DAO.headQ.inventory.InventoryOrderProductDAOImpl;
import com.onlineMIS.ORM.DAO.headQ.inventoryFlow.HeadQInventoryStockDAOImpl;
import com.onlineMIS.ORM.DAO.headQ.supplier.finance.FinanceSupplierService;
import com.onlineMIS.ORM.DAO.headQ.supplier.finance.SupplierAcctFlowDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.supplier.purchase.PurchaseOrderDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.supplier.purchase.PurchaseOrderProductDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.supplier.purchase.SupplierPurchaseService;
import com.onlineMIS.ORM.DAO.headQ.supplier.supplierMgmt.HeadQSupplierDaoImpl;
import com.onlineMIS.ORM.DAO.shared.expense.ExpenseDaoImpl;
import com.onlineMIS.ORM.entity.chainS.user.ChainRoleType;
import com.onlineMIS.ORM.entity.chainS.user.ChainStore;
import com.onlineMIS.ORM.entity.chainS.user.ChainUserInfor;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.BelongToVO;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Brand;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Category;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Color;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.HeadQTag;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.HeadQTagGroup;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Product;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.ProductBarcode;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.ProductBarcodePic;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Quarter;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.TagsVO;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.Year;
import com.onlineMIS.ORM.entity.headQ.custMgmt.HeadQCust;
import com.onlineMIS.ORM.entity.headQ.custMgmt.HeadQCustTemplate;
import com.onlineMIS.ORM.entity.headQ.custMgmt.HeadQCustVO;
import com.onlineMIS.ORM.entity.headQ.finance.ChainAcctFlowReportItem;
import com.onlineMIS.ORM.entity.headQ.finance.ExpenseHqType;
import com.onlineMIS.ORM.entity.headQ.finance.FinanceBill;
import com.onlineMIS.ORM.entity.headQ.finance.FinanceBillCustomerVO;
import com.onlineMIS.ORM.entity.headQ.finance.HeadQAcctFlow;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrder;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrderDTO;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrderProduct;
import com.onlineMIS.ORM.entity.headQ.inventory.SalesStatisticsDTO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQCustAcctFlowReportItem;
import com.onlineMIS.ORM.entity.headQ.report.HeadQCustAcctFlowReportTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQCustAcctFlowTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQCustInforTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQDealingsReportVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQExpenseReportTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQExpenseRptElesVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQPurchaseStatisticReportItem;
import com.onlineMIS.ORM.entity.headQ.report.HeadQPurchaseStatisticReportItemVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQPurchaseStatisticsReportTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQReportItemVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSalesStatisticReportItem;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSalesStatisticReportItemVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSalesStatisticsReportTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSupplierAcctFlowReportItem;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSupplierAcctFlowReportTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSupplierAcctFlowTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSupplierInforTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadQUnitSalesReportVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadqDealingsReportTemplate;
import com.onlineMIS.ORM.entity.headQ.report.HeadqUnitReportDetailsVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadqUnitReportTemplate;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.FinanceBillSupplier;
import com.onlineMIS.ORM.entity.headQ.supplier.finance.SupplierAcctFlowReportItem;
import com.onlineMIS.ORM.entity.headQ.supplier.purchase.PurchaseOrder;
import com.onlineMIS.ORM.entity.headQ.supplier.supplierMgmt.HeadQSupplier;
import com.onlineMIS.ORM.entity.headQ.user.UserInfor;
import com.onlineMIS.ORM.entity.shared.expense.Expense;
import com.onlineMIS.action.headQ.finance.FinanceActionFormBean;
import com.onlineMIS.action.headQ.report.HeadQReportFormBean;
import com.onlineMIS.action.headQ.report.HeadQReportUIBean;
import com.onlineMIS.action.headQ.report.HqPeiHuoExpoertVO;
import com.onlineMIS.action.headQ.report.HqSalesStatisReportItem;
import com.onlineMIS.action.headQ.report.HqSalesStatisticReportItemVO;
import com.onlineMIS.action.headQ.report.HqTabletReportItemVO;
import com.onlineMIS.action.headQ.report.SalesRecordsVO;
import com.onlineMIS.common.Common_util;
import com.onlineMIS.common.DateUtils;
import com.onlineMIS.common.ExcelUtil;
import com.onlineMIS.common.LoginUserUtils;
import com.onlineMIS.common.loggerLocal;
import com.onlineMIS.filter.SystemParm;
import com.onlineMIS.sorter.ChainStatisticReportItemVOSorter;
import com.onlineMIS.sorter.HeadQExpenseReportSort;
import com.onlineMIS.sorter.HeadQStatisticReportItemSorter;
import com.onlineMIS.sorter.HeadQStatisticReportItemSumSorter;
import com.onlineMIS.sorter.HeadQStatisticReportItemVOSorter;
import com.onlineMIS.sorter.HqStatisticReportItemVOByQuantitySorter;
import com.onlineMIS.sorter.HqStatisticReportItemVOSorter;

import freemarker.core.Comment;

@Service
public class HeadQReportService {

	@Autowired
	private HeadQSupplierDaoImpl headQSupplierDaoImpl;
	
	@Autowired
	private PurchaseOrderProductDaoImpl purchaseOrderProductDaoImpl;
		
	@Autowired
	private YearDaoImpl yearDaoImpl;
	
	@Autowired
	private ChainStoreDaoImpl chainStoreDaoImpl;
	
	@Autowired
	private HeadQCustDaoImpl headQCustDaoImpl;

	
	@Autowired
	private QuarterDaoImpl quarterDaoImpl;
	
	@Autowired
	private BrandDaoImpl brandDaoImpl;
	
	@Autowired
	private ProductBarcodeDaoImpl productBarcodeDaoImpl;
	
	@Autowired
	private ProductDaoImpl productDaoImpl;
	
	@Autowired
	private InventoryOrderProductDAOImpl inventoryOrderProductDAOImpl;
	
	@Autowired
	private InventoryOrderDAOImpl inventoryOrderDAOImpl;
	
	@Autowired
	private FinanceSupplierService financeSupplierService;
	
	@Autowired
	private FinanceService financeService;
	
	@Autowired
	private HeadQAcctFlowDaoImpl headQAcctFlowDaoImpl;
	
	@Autowired
	private SupplierAcctFlowDaoImpl supplierAcctFlowDaoImpl;
	
	@Autowired
	private FinanceBillImpl financeBillImpl;
	
	@Autowired
	private ExpenseDaoImpl expenseDaoImpl;
	
	@Autowired
	private PurchaseOrderDaoImpl purchaseOrderDaoImpl;
	
	@Autowired
	private ColorDaoImpl colorDaoImpl;

	@Autowired
	private CategoryDaoImpl categoryDaoImpl;
	
	@Autowired
	private ProductBarcodePicDaoImpl productBarcodePicDaoImpl;
	
	private ExpenseHqTypeImpl expenseHqTypeImpl;
	
	@Autowired
	private LoginUserUtils loginUserUtils;
	
	@Autowired
	private HeadQTagDaoImpl headQTagDaoImpl;
	@Autowired
	private ChainAcctFlowDaoImpl chainAcctFlowDaoImpl;
	@Autowired
	private ChainFinanceTraceImpl chainFinanceTraceImpl;
	@Autowired
	private HeadQInventoryStockDAOImpl headQInventoryStockDAOImpl;
	
	/**
	 * 获取总部的采购报表数据
	 * @param parentId
	 * @param startDate
	 * @param endDate
	 * @param id
	 * @param year_ID
	 * @param quarter_ID
	 * @param brand_ID
	 * @return
	 */
	@Transactional
	public Response getPurchaseStatisticReptEles(int parentId, Date startDate,
			Date endDate, int supplierId, int yearId, int quarterId, int brandId,int reportType, int belongTo) {
		Response response = new Response();
		List<HeadQPurchaseStatisticReportItemVO> reportItems = new ArrayList<HeadQPurchaseStatisticReportItemVO>();
		String name = "";

		
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(PurchaseOrder.STATUS_COMPLETE);
		value_sale.add(Common_util.formStartDate(startDate));
		value_sale.add(Common_util.formEndDate(endDate));


		
		String whereClause = "";
		HeadQSupplier supplier = headQSupplierDaoImpl.getAllSupplierObj();
		if (supplierId != Common_util.ALL_RECORD_NEW){
			supplier = headQSupplierDaoImpl.get(supplierId, true);
			whereClause = " AND p.order.supplier.id = " + supplier.getId();
		}
		

		
		if (belongTo != Common_util.ALL_RECORD) {
			//货品归属
			whereClause += " AND p.pb.product.brand.belong_To = ?";
			value_sale.add(belongTo); 
		}

		if (parentId == 0){
			//@2. 根节点
			StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.order.type FROM PurchaseOrderProduct p " + 
			         " WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ?");

			sql.append(whereClause);
			sql.append(" GROUP BY p.order.type");
		
			List<Object> values = purchaseOrderProductDaoImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);

			//name = supplier.getName() + "("+Brand.belongToMap.get(belongTo) +"货品)";
			if (supplierId != Common_util.ALL_RECORD_NEW){
				name += supplier.getName() + " ";
			}
			if (belongTo != Common_util.ALL_RECORD) {
				name += Brand.belongToMap.get(belongTo) +"货品 ";
			} else {
				name += "所有货品 ";
			}
			name += Common_util.dateFormat.format(startDate) + "至" +  Common_util.dateFormat.format(endDate);
			
			
			
			HeadQPurchaseStatisticReportItemVO rootItem = new HeadQPurchaseStatisticReportItemVO(name, 1, supplierId, yearId, quarterId, brandId,0, HeadQReportItemVO.STATE_CLOSED);
			
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double cost = Common_util.getDouble(records[1]);
					//double wholeSales = Common_util.getDouble(records[2]);
					int type = Common_util.getInt(records[2]);
					
					rootItem.putValue(type, quantity, cost);
				}
				
				rootItem.reCalculate();
			}
			
			reportItems.add(rootItem);
		} else if (yearId == 0){
			//@2. 展开所有年份的库存信息
			StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.product.year.year_ID, p.order.type   FROM PurchaseOrderProduct p " + 
			         " WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ?");

			sql.append(whereClause);
			sql.append(" GROUP BY p.pb.product.year.year_ID, p.order.type");
			
			List<Object> values = purchaseOrderProductDaoImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);
			
			Map<Integer, HeadQPurchaseStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQPurchaseStatisticReportItemVO>();
			for (Object record : values ){
				Object[] records = (Object[])record;
				int quantity = Common_util.getInt(records[0]);
				double cost = Common_util.getDouble(records[1]);
				int yearIdDB = Common_util.getInt(records[2]);
				int type = Common_util.getInt(records[3]);
				
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(yearIdDB);
				if (levelOneItem != null){
					levelOneItem.putValue(type, quantity, cost);
				} else {
					Year year = yearDaoImpl.get(yearIdDB, true);
					name = year.getYear() + "年";
					
					levelOneItem = new HeadQPurchaseStatisticReportItemVO(name, parentId, supplierId, yearIdDB, quarterId, brandId,0, HeadQReportItemVO.STATE_CLOSED);
					levelOneItem.putValue(type, quantity, cost);
					
					dataMap.put(yearIdDB, levelOneItem);
				}
			}
			
			List<Integer> yearKey = new ArrayList<Integer>(dataMap.keySet());
			
			//1. 把基本对象放入
			//2. 计算总数
			for (Integer id : yearKey){
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(id);
				levelOneItem.reCalculate();

				reportItems.add(levelOneItem);
			}		
			
		} else if (quarterId == 0){
			//@2. 展开所有季的库存信息
			StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.product.quarter.quarter_ID, p.order.type  FROM PurchaseOrderProduct p " + 
			         " WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ? AND p.pb.product.year.year_ID =" + yearId);

			sql.append(whereClause);
			sql.append(" GROUP BY p.pb.product.quarter.quarter_ID, p.order.type");
			
			List<Object> values = purchaseOrderProductDaoImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);
			
			Map<Integer, HeadQPurchaseStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQPurchaseStatisticReportItemVO>();
			for (Object record : values ){
				Object[] records = (Object[])record;
				int quantity = Common_util.getInt(records[0]);
				double amount = Common_util.getDouble(records[1]);
				int quarterIdDB = Common_util.getInt(records[2]);
				int type = Common_util.getInt(records[3]);
				
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(quarterIdDB);
				if (levelOneItem != null){
					levelOneItem.putValue(type, quantity, amount);
				} else {
					Year year = yearDaoImpl.get(yearId, true);
					Quarter quarter = quarterDaoImpl.get(quarterIdDB, true);
					name = year.getYear() + "年 " + quarter.getQuarter_Name();
					
					levelOneItem = new HeadQPurchaseStatisticReportItemVO(name, parentId, supplierId, yearId, quarterIdDB, brandId,0, HeadQReportItemVO.STATE_CLOSED);
					levelOneItem.putValue(type, quantity, amount);
					
					dataMap.put(quarterIdDB, levelOneItem);
				}
			}
			
			List<Integer> quarterKey = new ArrayList<Integer>(dataMap.keySet());
			
			//1. 把基本对象放入
			//2. 计算总数
			for (Integer id : quarterKey){
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(id);
				levelOneItem.reCalculate();

				reportItems.add(levelOneItem);
			}		
		} else if (brandId == 0){
			//@2. 展开所有品牌的库存信息
			StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.product.brand.brand_ID, p.order.type FROM PurchaseOrderProduct p " + 
			         " WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ? AND p.pb.product.year.year_ID =" + yearId + " AND p.pb.product.quarter.quarter_ID =" + quarterId);

			sql.append(whereClause);
			sql.append(" GROUP BY p.pb.product.brand.brand_ID, p.order.type");
			
			List<Object> values = purchaseOrderProductDaoImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);
			
			Map<Integer, HeadQPurchaseStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQPurchaseStatisticReportItemVO>();
			for (Object record : values ){
				Object[] records = (Object[])record;
				int quantity = Common_util.getInt(records[0]);
				double amount = Common_util.getDouble(records[1]);
				int brandIdDB = Common_util.getInt(records[2]);
				int type = Common_util.getInt(records[3]);
				
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(brandIdDB);
				if (levelOneItem != null){
					levelOneItem.putValue(type, quantity, amount);
				} else {
					Brand brand = brandDaoImpl.get(brandIdDB, true);
					
					name = brand.getBrand_Name();
					
					String brandSupplier = brand.getSupplier();
					if (!StringUtils.isEmpty(brandSupplier)){
						name += " " + brandSupplier;
					}

					levelOneItem = new HeadQPurchaseStatisticReportItemVO(name, parentId, supplierId, yearId, quarterId, brandIdDB,0, HeadQReportItemVO.STATE_CLOSED);
					levelOneItem.putValue(type, quantity, amount);
					
					dataMap.put(brandIdDB, levelOneItem);
				}
			}
			
			List<Integer> brandKey = new ArrayList<Integer>(dataMap.keySet());
			
			//1. 把基本对象放入
			//2. 计算总数
			for (Integer id : brandKey){
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(id);
				levelOneItem.reCalculate();

				reportItems.add(levelOneItem);
			}	
			
			
		} else if (brandId != 0) {
			//@2. 展开所有品牌的库存信息
			StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.id, p.order.type FROM PurchaseOrderProduct p " + 
			         " WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ? AND p.pb.product.year.year_ID =" + yearId + " AND p.pb.product.quarter.quarter_ID =" + quarterId+ " AND p.pb.product.brand.brand_ID =" + brandId);

			sql.append(whereClause);
			sql.append(" GROUP BY p.pb.id, p.order.type");
			
			List<Object> values = purchaseOrderProductDaoImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);
			
			Map<Integer, HeadQPurchaseStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQPurchaseStatisticReportItemVO>();
			for (Object record : values ){
				Object[] records = (Object[])record;
				int quantity = Common_util.getInt(records[0]);
				double amount = Common_util.getDouble(records[1]);
				int pbId = Common_util.getInt(records[2]);
				int type = Common_util.getInt(records[3]);
				
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(pbId);
				if (levelOneItem != null){
					levelOneItem.putValue(type, quantity, amount);
				} else {
					ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
					Color color = pb.getColor();
					String colorName = "";
					if (color != null)
						colorName = color.getName();
					
					Product product = pb.getProduct();

					String sizeRange = product.getSizeRange();
					
					Category category = pb.getProduct().getCategory();
					
					name = Common_util.cutProductCode(pb.getProduct().getProductCode()) + colorName  + " " + sizeRange +  category.getCategory_Name();
					
					levelOneItem = new HeadQPurchaseStatisticReportItemVO(name, parentId, supplierId, yearId, quarterId, brandId, pbId, HeadQReportItemVO.STATE_OPEN);
					levelOneItem.putValue(type, quantity, amount);
					
					dataMap.put(pbId, levelOneItem);
				}
			}
			
			List<Integer> pbKey = new ArrayList<Integer>(dataMap.keySet());
			Collections.sort(pbKey);
			
			//1. 把基本对象放入
			//2. 计算总数
			for (Integer id : pbKey){
				HeadQPurchaseStatisticReportItemVO levelOneItem = dataMap.get(id);
				levelOneItem.reCalculate();

				reportItems.add(levelOneItem);
			}
		}

		Collections.sort(reportItems, new HeadQStatisticReportItemVOSorter());
	    response.setReturnValue(reportItems);
	    return response;
	}
	
	/**
	 * 获取hq expense rpt eles
	 * @param parentId
	 * @param startDate
	 * @param endDate
	 * @param parentExpenseId
	 * @return
	 */
	public Response getHQExpenseRptEles(int parentId,Date startDate, Date endDate, int parentExpenseId ){
		Response response = new Response();
		List<HeadQExpenseRptElesVO> reportItems = new ArrayList<HeadQExpenseRptElesVO>();
		
		if (startDate == null){
			response.setReturnValue(reportItems);
			return response;
		}
		
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(startDate);
		value_sale.add(endDate);
		value_sale.add(Expense.statusE.NORMAL.getValue());
	
		
		String name = "总计";
		
		if (parentId == 0){
			String hql = "SELECT feeType, sum(amount)  FROM Expense WHERE expenseDate BETWEEN ? AND ? AND status =? AND entity IS null GROUP BY feeType";
			
			List<Object> values = expenseDaoImpl.executeHQLSelect(hql, value_sale.toArray(), null, true);
			
			HeadQExpenseRptElesVO rootItem = new HeadQExpenseRptElesVO(name, 1,  HeadQSalesStatisticReportItemVO.STATE_CLOSED);
			
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int feeType = Common_util.getInt(records[0]);
					double amount = Common_util.getDouble(records[1]);

					rootItem.putValue(feeType, amount);
				}
			}
			
			reportItems.add(rootItem);
		} else if (parentExpenseId == 0){
			String hql = "SELECT ExpenseHqType.parentId, feeType, sum(amount)  FROM Expense WHERE expenseDate BETWEEN ? AND ? AND status =? AND entity IS null  GROUP BY ExpenseHqType.parentId,feeType";
			
			List<Object> values = expenseDaoImpl.executeHQLSelect(hql, value_sale.toArray(), null, true);
			
			Map<Integer, HeadQExpenseRptElesVO> resultMap = new HashMap<Integer, HeadQExpenseRptElesVO>();
			
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int expenseParentId = Common_util.getInt(records[0]);
					int feeType = Common_util.getInt(records[1]);
					double amount = Common_util.getDouble(records[2]);

					HeadQExpenseRptElesVO rootItem = resultMap.get(expenseParentId);
					if (rootItem == null){
						rootItem = new HeadQExpenseRptElesVO(name, 1,  HeadQSalesStatisticReportItemVO.STATE_CLOSED);
						ExpenseHqType type = expenseHqTypeImpl.get(expenseParentId, true);
						rootItem.setName(type.getName());
						rootItem.setExpenseTypeParentId(expenseParentId);
					}
						
					rootItem.putValue(feeType, amount);
					
					resultMap.put(expenseParentId, rootItem);
				}
			}
			
			for (HeadQExpenseRptElesVO rootItem : resultMap.values())
			     reportItems.add(rootItem);
			
			Collections.sort(reportItems, new HeadQExpenseReportSort());
		} else {
			value_sale.add(parentExpenseId);
			String hql = "SELECT ExpenseHqType.id, feeType, sum(amount)  FROM Expense WHERE expenseDate BETWEEN ? AND ? AND status =? AND entity IS null AND ExpenseHqType.parentId = ?   GROUP BY ExpenseHqType.id,feeType";
			
			List<Object> values = expenseDaoImpl.executeHQLSelect(hql, value_sale.toArray(), null, true);
			
			Map<Integer, HeadQExpenseRptElesVO> resultMap = new HashMap<Integer, HeadQExpenseRptElesVO>();
			
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int ExpenseHqTypeId = Common_util.getInt(records[0]);
					int feeType = Common_util.getInt(records[1]);
					double amount = Common_util.getDouble(records[2]);

					HeadQExpenseRptElesVO rootItem = resultMap.get(ExpenseHqTypeId);
					if (rootItem == null){
						rootItem = new HeadQExpenseRptElesVO(name, 1,  HeadQSalesStatisticReportItemVO.STATE_OPEN);
						ExpenseHqType type = expenseHqTypeImpl.get(ExpenseHqTypeId, true);
						rootItem.setName(type.getName());
						rootItem.setExpenseTypeId(ExpenseHqTypeId);
					}
						
					rootItem.putValue(feeType, amount);
					
					resultMap.put(ExpenseHqTypeId, rootItem);
				}
			}
			
			for (HeadQExpenseRptElesVO rootItem : resultMap.values())
			     reportItems.add(rootItem);
			
			Collections.sort(reportItems, new HeadQExpenseReportSort());
		}
		
	    response.setReturnValue(reportItems);
	    return response;
	}
	
	/**
	 * 获取销售统计报表的信息
	 * @param parentId
	 * @param chain_id
	 * @param year_ID
	 * @param quarter_ID
	 * @param brand_ID
	 * @param userInfor
	 * @return
	 */
	public Response getSalesStatisticReptEles(int parentId,Date startDate, Date endDate, int custId, int yearId, int quarterId, int brandId) {
		Response response = new Response();
		List<HeadQSalesStatisticReportItemVO> reportItems = new ArrayList<HeadQSalesStatisticReportItemVO>();
		
		if (startDate == null){
			response.setReturnValue(reportItems);
			return response;
		}
		
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);
		value_sale.add(Common_util.formStartDate(startDate));
		value_sale.add(Common_util.formEndDate(endDate));

		String whereClause = "";
		
		HeadQCust cust = headQCustDaoImpl.getAllCustObj();
		if (custId != Common_util.ALL_RECORD_NEW){
			cust = headQCustDaoImpl.get(custId, true);
			whereClause = " AND p.order.cust.id = " + cust.getId();
		}

		String name = "";

		if (parentId == 0){
			//@2. 根节点
			name = cust.getName();
			
			String whereClause2 = "";
			if (custId != Common_util.ALL_RECORD_NEW){
				cust = headQCustDaoImpl.get(custId, true);
				whereClause2 = " AND cust.id = " + cust.getId();
			}
			
			String criteria = "SELECT SUM(quantity), SUM(recCost * quantity), SUM(wholeSalePrice * quantity), SUM(p.order.totalDiscount), p.order.order_type FROM InventoryOrderProduct p WHERE p.order.order_Status = ? AND p.order.order_EndTime BETWEEN ? AND ? "+ whereClause + " GROUP BY p.order.order_type";
			String criteria2 = "SELECT  SUM(totalDiscount), order_type FROM InventoryOrder WHERE order_Status = ? " + whereClause2 + " AND  order_EndTime BETWEEN ? AND ?  GROUP BY order_type";
			List<Object> values = inventoryOrderProductDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
			
			HeadQSalesStatisticReportItemVO rootItem = new HeadQSalesStatisticReportItemVO(name, 1, custId, yearId, quarterId, brandId,0, HeadQSalesStatisticReportItemVO.STATE_CLOSED);
			
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double cost = Common_util.getDouble(records[1]);
					double wholeSale = Common_util.getDouble(records[2]);
	
					int type = Common_util.getInt(records[4]);
					
					rootItem.putValue(quantity, type, wholeSale, cost);
				}
				
				rootItem.reCalculate();
			}
			
			List<Object> values2 = inventoryOrderProductDAOImpl.executeHQLSelect(criteria2, value_sale.toArray(), null, true);
			
			
			if (values2 != null){
				for (Object record : values2 ){
					Object[] records = (Object[])record;
					double totalDiscount = Common_util.getDouble(records[0]);
					int type = Common_util.getInt(records[1]);
					
					rootItem.putDiscount(type, totalDiscount);
				}
			}
			
			reportItems.add(rootItem);
		} else if (yearId == 0){
			//@2. 展开所有年份的库存信息
			String criteria = "SELECT SUM(quantity), SUM(recCost * quantity), SUM(wholeSalePrice * quantity), p.productBarcode.product.year.year_ID, p.order.order_type FROM InventoryOrderProduct p WHERE p.order.order_Status = ? AND p.order.order_EndTime BETWEEN ? AND ?  "+ whereClause + " GROUP BY p.productBarcode.product.year.year_ID, p.order.order_type";
			List<Object> values = inventoryOrderProductDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
			
			
			if (values != null){
				Map<Integer, HeadQSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQSalesStatisticReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double cost = Common_util.getDouble(records[1]);
					double wholeSale = Common_util.getDouble(records[2]);
					int yearIdDB = Common_util.getInt(records[3]);
					int type = Common_util.getInt(records[4]);
					
					HeadQSalesStatisticReportItemVO levelFour = dataMap.get(yearIdDB);
					if (levelFour != null){
						levelFour.putValue(quantity, type, wholeSale, cost);
					} else {
						Year year = yearDaoImpl.get(yearIdDB, true);
						
						name = year.getYear() + "年";
						
						levelFour = new HeadQSalesStatisticReportItemVO(name, parentId, custId, yearIdDB, quarterId, brandId,0, HeadQSalesStatisticReportItemVO.STATE_CLOSED);

						levelFour.putValue(quantity, type, wholeSale, cost);
					}
					
					dataMap.put(yearIdDB, levelFour);
				}
				
				List<Integer> yearIds = new ArrayList<Integer>(dataMap.keySet());
				
				//1. 把基本对象放入
				for (Integer id : yearIds){
					HeadQSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
					levelFourItem.reCalculate();

					reportItems.add(levelFourItem);
				}	
			}
			
			
		} else if (quarterId == 0){
			//@2. 展开所有季的库存信息
			String criteria = "SELECT SUM(quantity), SUM(recCost * quantity), SUM(wholeSalePrice * quantity), p.productBarcode.product.quarter.quarter_ID, p.order.order_type FROM InventoryOrderProduct p WHERE p.order.order_Status = ? AND p.order.order_EndTime BETWEEN ? AND ?  AND p.productBarcode.product.year.year_ID = " + yearId + whereClause + " GROUP BY p.productBarcode.product.quarter.quarter_ID, p.order.order_type";
			List<Object> values = inventoryOrderProductDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
			
			
			if (values != null){
				Map<Integer, HeadQSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQSalesStatisticReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double cost = Common_util.getDouble(records[1]);
					double wholeSale = Common_util.getDouble(records[2]);
					int quarterIdDB = Common_util.getInt(records[3]);
					int type = Common_util.getInt(records[4]);
					
					HeadQSalesStatisticReportItemVO levelFour = dataMap.get(quarterIdDB);
					if (levelFour != null){
						levelFour.putValue(quantity, type, wholeSale, cost);
					} else {
						Year year = yearDaoImpl.get(yearId, true);
						Quarter quarter = quarterDaoImpl.get(quarterIdDB, true);
						name = year.getYear() + "年" + quarter.getQuarter_Name();
						
						levelFour = new HeadQSalesStatisticReportItemVO(name, parentId, custId, yearId, quarterIdDB, brandId,0,  HeadQSalesStatisticReportItemVO.STATE_CLOSED);

						levelFour.putValue(quantity, type, wholeSale, cost);
					}
					
					dataMap.put(quarterIdDB, levelFour);
				}
				
				List<Integer> quarterIds = new ArrayList<Integer>(dataMap.keySet());
				
				//1. 把基本对象放入
				for (Integer id : quarterIds){
					HeadQSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
					levelFourItem.reCalculate();

					reportItems.add(levelFourItem);
				}	
			}
		} else if (brandId == 0){
			//@2. 展开所有品牌的库存信息
			String criteria = "SELECT SUM(quantity), SUM(recCost * quantity), SUM(wholeSalePrice * quantity),  p.productBarcode.product.brand.brand_ID, p.order.order_type FROM InventoryOrderProduct p WHERE p.order.order_Status = ? AND p.order.order_EndTime BETWEEN ? AND ?  AND p.productBarcode.product.year.year_ID = " + yearId + " AND p.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause + " GROUP BY p.productBarcode.product.brand.brand_ID, p.order.order_type";
			List<Object> values = inventoryOrderProductDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
			
			
			if (values != null){
				Map<Integer, HeadQSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQSalesStatisticReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double cost = Common_util.getDouble(records[1]);
					double wholeSale = Common_util.getDouble(records[2]);
					int brandIdDB = Common_util.getInt(records[3]);
					int type = Common_util.getInt(records[4]);
					
					HeadQSalesStatisticReportItemVO levelFour = dataMap.get(brandIdDB);
					if (levelFour != null){
						levelFour.putValue(quantity, type, wholeSale, cost);
					} else {
						Brand brand = brandDaoImpl.get(brandIdDB, true);

						String pinyin = brand.getPinyin();
						if (!StringUtils.isEmpty(pinyin)){
							name = pinyin.substring(0, 1) + " ";
						}
						
						 name += brand.getBrand_Name();
						
						levelFour = new HeadQSalesStatisticReportItemVO(name, parentId, custId, yearId, quarterId, brandIdDB,0, HeadQSalesStatisticReportItemVO.STATE_CLOSED);
						levelFour.putValue(quantity, type, wholeSale, cost);
					}
					
					dataMap.put(brandIdDB, levelFour);
				}
				
				List<Integer> brandIds = new ArrayList<Integer>(dataMap.keySet());
				
				//1. 把基本对象放入
				for (Integer id : brandIds){
					HeadQSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
					levelFourItem.reCalculate();

					reportItems.add(levelFourItem);
				}	

			}
		} else if (brandId != 0) {
			//@2. 展开所有品牌的库存信息
			String criteria = "SELECT SUM(quantity), SUM(recCost * quantity), SUM(wholeSalePrice * quantity), p.productBarcode.id, p.order.order_type FROM InventoryOrderProduct p WHERE p.order.order_Status = ? AND p.order.order_EndTime BETWEEN ? AND ? AND p.productBarcode.product.year.year_ID = " + yearId + " AND p.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause + " AND p.productBarcode.product.brand.brand_ID = " + brandId + " GROUP BY p.productBarcode.id, p.order.order_type";
			List<Object> values = inventoryOrderProductDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);

			if (values != null){
				Map<Integer, HeadQSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HeadQSalesStatisticReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double cost = Common_util.getDouble(records[1]);
					double wholeSale = Common_util.getDouble(records[2]);
					int pbId = Common_util.getInt(records[3]);
					int type = Common_util.getInt(records[4]);
					
					HeadQSalesStatisticReportItemVO levelFour = dataMap.get(pbId);
					if (levelFour != null){
						levelFour.putValue(quantity, type, wholeSale, cost);
					} else {
						ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
						String barcode = pb.getBarcode();
						Color color = pb.getColor();
						String colorName = "";
						if (color != null)
							colorName = color.getName();
						
						Product product = pb.getProduct();

						String sizeRange = product.getSizeRange();
						
						Category category = product.getCategory();
						name = Common_util.cutProductCode(pb.getProduct().getProductCode()) + colorName + " " + sizeRange +  category.getCategory_Name();
						
						levelFour = new HeadQSalesStatisticReportItemVO(name, parentId, custId, yearId, quarterId, brandId, pbId, HeadQSalesStatisticReportItemVO.STATE_OPEN);
						levelFour.putValue(quantity, type, wholeSale, cost);
						levelFour.setBarcode(barcode);
					}
					
					dataMap.put(pbId, levelFour);
				}
				
				List<Integer> pbIds = new ArrayList<Integer>(dataMap.keySet());
				
				//1. 把基本对象放入
				for (Integer id : pbIds){
					HeadQSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
					levelFourItem.reCalculate();

					reportItems.add(levelFourItem);
				}	
			}
		}
		Collections.sort(reportItems, new HeadQStatisticReportItemVOSorter());
	    response.setReturnValue(reportItems);
	    return response;
	}


	/**
	 * 下
	 * @param parentId
	 * @param supplier
	 * @param year_ID
	 * @param quarter_ID
	 * @param brand_ID
	 * @param string
	 * @return
	 */
	@Transactional
	public Response downloadPurchaseExcelReport(int parentId, int supplierId, int yearId,
			int quarterId, int brandId, Date startDate,Date endDate,String excelPath) {
		Response response = new Response();
		
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(PurchaseOrder.STATUS_COMPLETE);
		value_sale.add(Common_util.formStartDate(startDate));
		value_sale.add(Common_util.formEndDate(endDate));

		String whereClause = "";
		String rptDesp = "";

		StringBuffer sqlSum = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.product.year.year_ID, p.pb.product.quarter.quarter_ID, p.pb.product.brand.brand_ID, p.order.type FROM PurchaseOrderProduct p WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ?") ;
		StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.id, p.order.type,p.order.creationTime FROM PurchaseOrderProduct p WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ?") ;

		if (yearId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.pb.product.year.year_ID =" + yearId;
			sql.append(where);
			sqlSum.append(where);
			Year year = yearDaoImpl.get(yearId, true);
			
			rptDesp += " " + year.getYear();
		}
		
		if (quarterId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.pb.product.quarter.quarter_ID =" + quarterId;
			sql.append(where);
			sqlSum.append(where);
			Quarter quarter = quarterDaoImpl.get(quarterId, true);
			
			rptDesp += " " + quarter.getQuarter_Name();
		}
		
		if (brandId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.pb.product.brand.brand_ID =" + brandId;
			sql.append(where);
			sqlSum.append(where);
			Brand brand = brandDaoImpl.get(brandId, true);
			
			rptDesp += " " + brand.getBrand_Name();
		}

		HeadQSupplier supplier = headQSupplierDaoImpl.getAllSupplierObj();
		if (supplierId != Common_util.ALL_RECORD_NEW){
			supplier = headQSupplierDaoImpl.get(supplierId, true);
			whereClause = " AND p.order.supplier.id = " + supplier.getId();
		}
		rptDesp = supplier.getName() + " " + rptDesp;
		
		sql.append(whereClause);
		sqlSum.append(whereClause);
		sql.append(" GROUP BY p.pb.id, p.order.type");
		
		List<Object> values = purchaseOrderProductDaoImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);
		
		Map<Integer, HeadQPurchaseStatisticReportItem> dataMap = new HashMap<Integer, HeadQPurchaseStatisticReportItem>();
		HeadQPurchaseStatisticReportItem totalItem = new HeadQPurchaseStatisticReportItem();
		for (Object record : values ){
			Object[] records = (Object[])record;
			int quantity = Common_util.getInt(records[0]);
			double amount = Common_util.getDouble(records[1]);
			int pbId = Common_util.getInt(records[2]);
			int type = Common_util.getInt(records[3]);
			String date = Common_util.getString(records[4]);
			
			totalItem.add(type, quantity, amount);
			
			HeadQPurchaseStatisticReportItem levelOneItem = dataMap.get(pbId);
			if (levelOneItem != null){
				levelOneItem.add(type, quantity, amount);
			} else {
				ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
				
				levelOneItem = new HeadQPurchaseStatisticReportItem(type, quantity, amount, pb,date);
				
				dataMap.put(pbId, levelOneItem);
			}
		}
		
		List<HeadQPurchaseStatisticReportItem> items = new ArrayList<HeadQPurchaseStatisticReportItem>(dataMap.values());
		
		Collections.sort(items, new HeadQStatisticReportItemSorter());
		
		//2. 准备品牌汇总数据
		sqlSum.append(" GROUP BY p.pb.product.year.year_ID,p.pb.product.quarter.quarter_ID, p.pb.product.brand.brand_ID, p.order.type");
        
		List<Object> valuesSum = purchaseOrderProductDaoImpl.executeHQLSelect(sqlSum.toString(), value_sale.toArray(), null, true);

		
		Map<String, HeadQPurchaseStatisticReportItem> dataMapSum = new HashMap<String, HeadQPurchaseStatisticReportItem>();

		for (Object record : valuesSum ){
			Object[] records = (Object[])record;
			int quantity = Common_util.getInt(records[0]);
			double amount = Common_util.getDouble(records[1]);
			int yearIdDB = Common_util.getInt(records[2]);
			int quarterIdDB = Common_util.getInt(records[3]);
			int brandIdDB = Common_util.getInt(records[4]);
			int type = Common_util.getInt(records[5]);
			
			String key = yearIdDB + "@" + quarterIdDB + "@" + brandIdDB;
			
			HeadQPurchaseStatisticReportItem levelOneItem = dataMapSum.get(key);
			if (levelOneItem != null){
				levelOneItem.add(type, quantity, amount);
			} else {
				Year year = yearDaoImpl.get(yearIdDB, true);
				Quarter quarter = quarterDaoImpl.get(quarterIdDB, true);
				Brand brand = brandDaoImpl.get(brandIdDB, true);
				
				levelOneItem = new HeadQPurchaseStatisticReportItem(type, quantity, amount, year, quarter, brand);
				
				dataMapSum.put(key, levelOneItem);
			}
		}
		
		List<HeadQPurchaseStatisticReportItem> itemsSum = new ArrayList<HeadQPurchaseStatisticReportItem>(dataMapSum.values());
		
		Collections.sort(itemsSum, new HeadQStatisticReportItemSumSorter());
		
		//3. 准备excel 报表
		try {
		    HeadQPurchaseStatisticsReportTemplate rptTemplate = new HeadQPurchaseStatisticsReportTemplate(items, itemsSum, totalItem, rptDesp, excelPath, startDate, endDate);
		    HSSFWorkbook wb = new HSSFWorkbook();
			wb = rptTemplate.process();
									
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}
	
	
	/**
	 * 下
	 * @param parentId
	 * @param supplier
	 * @param year_ID
	 * @param quarter_ID
	 * @param brand_ID
	 * @param string
	 * @return
	 */
	@Transactional
	public Response downloadPurchaseLevelReport(List<HeadQPurchaseStatisticReportItemVO> itemVOs, Date startDate,Date endDate,String excelPath) throws IOException {
		Response response = new Response();
		
		HeadQPurchaseStatisticsReportTemplate rptTemplate = new HeadQPurchaseStatisticsReportTemplate(itemVOs ,excelPath, startDate, endDate);
		HSSFWorkbook wb = new HSSFWorkbook();
		wb = rptTemplate.processLevel();
								
		ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
		
		response.setReturnValue(byteArrayInputStream);
		response.setReturnCode(Response.SUCCESS);
		return response;
	}
	
	/**
	 * 下
	 * @param parentId
	 * @param supplier
	 * @param year_ID
	 * @param quarter_ID
	 * @param brand_ID
	 * @param string
	 * @return
	 */
	@Transactional
	public Response downloadPurchaseDetailsExcelReport(int parentId, int supplierId, int yearId,
			int quarterId, int brandId, Date startDate,Date endDate,String excelPath) {
		Response response = new Response();
		
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(PurchaseOrder.STATUS_COMPLETE);
		value_sale.add(Common_util.formStartDate(startDate));
		value_sale.add(Common_util.formEndDate(endDate));

		String whereClause = "";
		String rptDesp = "";

		StringBuffer sqlSum = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.product.year.year_ID, p.pb.product.quarter.quarter_ID, p.pb.product.brand.brand_ID, p.order.type FROM PurchaseOrderProduct p WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ?") ;
		StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), p.pb.id, p.order.type,p.order.creationTime FROM PurchaseOrderProduct p WHERE p.order.status = ? AND p.order.creationTime BETWEEN ? AND ?") ;

		if (yearId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.pb.product.year.year_ID =" + yearId;
			sql.append(where);
			sqlSum.append(where);
			Year year = yearDaoImpl.get(yearId, true);
			
			rptDesp += " " + year.getYear();
		}
		
		if (quarterId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.pb.product.quarter.quarter_ID =" + quarterId;
			sql.append(where);
			sqlSum.append(where);
			Quarter quarter = quarterDaoImpl.get(quarterId, true);
			
			rptDesp += " " + quarter.getQuarter_Name();
		}
		
		if (brandId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.pb.product.brand.brand_ID =" + brandId;
			sql.append(where);
			sqlSum.append(where);
			Brand brand = brandDaoImpl.get(brandId, true);
			
			rptDesp += " " + brand.getBrand_Name();
		}

		HeadQSupplier supplier = headQSupplierDaoImpl.getAllSupplierObj();
		if (supplierId != Common_util.ALL_RECORD_NEW){
			supplier = headQSupplierDaoImpl.get(supplierId, true);
			whereClause = " AND p.order.supplier.id = " + supplier.getId();
		}
		rptDesp = supplier.getName() + " " + rptDesp;
		
		sql.append(whereClause);
		sqlSum.append(whereClause);
		sql.append(" GROUP BY p.pb.id, p.order.type,p.order.creationTime");
		
		List<Object> values = purchaseOrderProductDaoImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);
		
		HeadQPurchaseStatisticReportItem totalItem = new HeadQPurchaseStatisticReportItem();
		List<HeadQPurchaseStatisticReportItem> items = new ArrayList<HeadQPurchaseStatisticReportItem>();
		for (Object record : values ){
			Object[] records = (Object[])record;
			int quantity = Common_util.getInt(records[0]);
			double amount = Common_util.getDouble(records[1]);
			int pbId = Common_util.getInt(records[2]);
			int type = Common_util.getInt(records[3]);
			String date = Common_util.getString(records[4]);
			
			totalItem.add(type, quantity, amount);
						
			ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
				
			HeadQPurchaseStatisticReportItem levelOneItem = new HeadQPurchaseStatisticReportItem(type, quantity, amount, pb,date);
			items.add(levelOneItem);				
			
		}
				
		Collections.sort(items, new HeadQStatisticReportItemSorter());
		
		//2. 准备品牌汇总数据
		sqlSum.append(" GROUP BY p.pb.product.year.year_ID,p.pb.product.quarter.quarter_ID, p.pb.product.brand.brand_ID, p.order.type");
        
		List<Object> valuesSum = purchaseOrderProductDaoImpl.executeHQLSelect(sqlSum.toString(), value_sale.toArray(), null, true);

		
		Map<String, HeadQPurchaseStatisticReportItem> dataMapSum = new HashMap<String, HeadQPurchaseStatisticReportItem>();

		for (Object record : valuesSum ){
			Object[] records = (Object[])record;
			int quantity = Common_util.getInt(records[0]);
			double amount = Common_util.getDouble(records[1]);
			int yearIdDB = Common_util.getInt(records[2]);
			int quarterIdDB = Common_util.getInt(records[3]);
			int brandIdDB = Common_util.getInt(records[4]);
			int type = Common_util.getInt(records[5]);
			
			String key = yearIdDB + "@" + quarterIdDB + "@" + brandIdDB;
			
			HeadQPurchaseStatisticReportItem levelOneItem = dataMapSum.get(key);
			if (levelOneItem != null){
				levelOneItem.add(type, quantity, amount);
			} else {
				Year year = yearDaoImpl.get(yearIdDB, true);
				Quarter quarter = quarterDaoImpl.get(quarterIdDB, true);
				Brand brand = brandDaoImpl.get(brandIdDB, true);
				
				levelOneItem = new HeadQPurchaseStatisticReportItem(type, quantity, amount, year, quarter, brand);
				
				dataMapSum.put(key, levelOneItem);
			}
		}
		
		List<HeadQPurchaseStatisticReportItem> itemsSum = new ArrayList<HeadQPurchaseStatisticReportItem>(dataMapSum.values());
		
		Collections.sort(itemsSum, new HeadQStatisticReportItemSumSorter());
		
		//3. 准备excel 报表
		try {
		    HeadQPurchaseStatisticsReportTemplate rptTemplate = new HeadQPurchaseStatisticsReportTemplate(items, itemsSum, totalItem, rptDesp, excelPath, startDate, endDate);
		    HSSFWorkbook wb = new HSSFWorkbook();

			wb = rptTemplate.processDetails();
									
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}
	
	/**
	 * 下
	 * @param parentId
	 * @param supplier
	 * @param year_ID
	 * @param quarter_ID
	 * @param brand_ID
	 * @param string
	 * @return
	 */
	@Transactional
	public Response downloadSalesExcelReport(int parentId, int custId, int yearId,
			int quarterId, int brandId, Date startDate,Date endDate,String excelPath) {
		Response response = new Response();
		
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);
		value_sale.add(Common_util.formStartDate(startDate));
		value_sale.add(Common_util.formEndDate(endDate));

		String whereClause = "";
		String rptDesp = "";

		//1. 下载明细数据
		StringBuffer sql_sum = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), SUM(wholeSalePrice * quantity),p.productBarcode.product.year.year_ID,p.productBarcode.product.quarter.quarter_ID,p.productBarcode.product.brand.brand_ID, p.order.order_type FROM InventoryOrderProduct p WHERE p.order.order_Status = ? AND p.order.order_EndTime BETWEEN ? AND ? ") ;
		StringBuffer sql = new StringBuffer("SELECT SUM(quantity), SUM(recCost * quantity), SUM(wholeSalePrice * quantity),p.productBarcode.id, p.order.order_type FROM InventoryOrderProduct p  WHERE p.order.order_Status = ? AND p.order.order_EndTime BETWEEN ? AND ? ") ;

		if (yearId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.productBarcode.product.year.year_ID =" + yearId;
			sql.append(where);
			sql_sum.append(where);
			Year year = yearDaoImpl.get(yearId, true);
			
			rptDesp += " " + year.getYear();
		}
		
		if (quarterId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.productBarcode.product.quarter.quarter_ID =" + quarterId;
			sql.append(where);
			sql_sum.append(where);
			Quarter quarter = quarterDaoImpl.get(quarterId, true);
			
			rptDesp += " " + quarter.getQuarter_Name();
		}
		
		if (brandId != Common_util.ALL_RECORD_NEW){
			String where = " AND p.productBarcode.product.brand.brand_ID =" + brandId;
			sql.append(where);
			sql_sum.append(where);
			Brand brand = brandDaoImpl.get(brandId, true);
			
			rptDesp += " " + brand.getBrand_Name();
		}

		HeadQCust cust = headQCustDaoImpl.getAllCustObj();
		if (custId != Common_util.ALL_RECORD_NEW){
			cust = headQCustDaoImpl.get(custId, true);
			whereClause = " AND p.order.client_id = " + custId;
		}
		rptDesp = cust.getName() + " " + rptDesp;
		
		sql.append(whereClause);
	
        
		sql.append(" GROUP BY p.productBarcode.id, p.order.order_type");
		
		List<Object> values = inventoryOrderProductDAOImpl.executeHQLSelect(sql.toString(), value_sale.toArray(), null, true);
		
		Map<Integer, HeadQSalesStatisticReportItem> dataMap = new HashMap<Integer, HeadQSalesStatisticReportItem>();
		HeadQSalesStatisticReportItem totalItem = new HeadQSalesStatisticReportItem();
		if (!values.isEmpty()) {
			for (Object record : values ){
				Object[] records = (Object[])record;
				int quantity = Common_util.getInt(records[0]);
				double cost = Common_util.getDouble(records[1]);
				double amount = Common_util.getDouble(records[2]);
				int pbId = Common_util.getInt(records[3]);
				int type = Common_util.getInt(records[4]);
				
				totalItem.add(type, quantity, amount, cost);
				
				HeadQSalesStatisticReportItem levelOneItem = dataMap.get(pbId);
				if (levelOneItem != null){
					levelOneItem.add(type, quantity, amount, cost);
				} else {
					ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
					
					levelOneItem = new HeadQSalesStatisticReportItem(type, quantity, amount, cost, pb);
					
					dataMap.put(pbId, levelOneItem);
				}
			}
		}
		
		List<HeadQSalesStatisticReportItem> items = new ArrayList<HeadQSalesStatisticReportItem>(dataMap.values());
		Collections.sort(items, new HeadQStatisticReportItemSorter());
		
		
		//2。下载以品牌为基础的明细数据
		sql_sum.append(" GROUP BY p.productBarcode.product.year.year_ID,p.productBarcode.product.quarter.quarter_ID, p.productBarcode.product.brand.brand_ID, p.order.order_type");
        List<Object> values_sum = inventoryOrderProductDAOImpl.executeHQLSelect(sql_sum.toString(), value_sale.toArray(), null, true);
		
		Map<String, HeadQSalesStatisticReportItem> dataMap_sum = new HashMap<String, HeadQSalesStatisticReportItem>();

		for (Object record : values_sum ){
			Object[] records = (Object[])record;
			int quantity = Common_util.getInt(records[0]);
			double cost = Common_util.getDouble(records[1]);
			double amount = Common_util.getDouble(records[2]);
			int yearIdDB = Common_util.getInt(records[3]);
			int quarterIdDB = Common_util.getInt(records[4]);
			int brandIdDB = Common_util.getInt(records[5]);
			int type = Common_util.getInt(records[6]);
			
			//System.out.println(yearIdDB + "," + quarterIdDB + "," + brandIdDB + "," + type + "," + quantity + "," + cost + "," + amount);
			
			String key = yearIdDB + "@" + quarterIdDB + "@" + brandIdDB;
			
			HeadQSalesStatisticReportItem levelOneItem = dataMap_sum.get(key);
			if (levelOneItem != null){
				levelOneItem.add(type, quantity, amount, cost);
			} else {
				Year year = yearDaoImpl.get(yearIdDB, true);
				Quarter quarter = quarterDaoImpl.get(quarterIdDB, true);
				Brand brand = brandDaoImpl.get(brandIdDB, true);
				
				levelOneItem = new HeadQSalesStatisticReportItem(type, quantity, amount, cost, year, quarter,brand);
				
				dataMap_sum.put(key, levelOneItem);
			}
		}
		
		List<HeadQSalesStatisticReportItem> items_sum = new ArrayList<HeadQSalesStatisticReportItem>(dataMap_sum.values());
		Collections.sort(items_sum, new HeadQStatisticReportItemSumSorter());
		
		//3. 准备excel 报表
		try {
		    HeadQSalesStatisticsReportTemplate rptTemplate = new HeadQSalesStatisticsReportTemplate(items, items_sum, totalItem, rptDesp, excelPath, startDate, endDate);
			HSSFWorkbook wb = rptTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}
	
	/**
	 * 下在客户信息成excel

	 * @return
	 */
	@Transactional
	public Response downloadCustInforExcelReport(String excelPath) {
		Response response = new Response();

		List<HeadQCust> custs = headQCustDaoImpl.getAll(true);

		//2. 准备excel 报表
		try {
		    HeadQCustInforTemplate rptTemplate = new HeadQCustInforTemplate(custs, excelPath);
			HSSFWorkbook wb = rptTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}

	/**
	 * 下在供应商信息成excel

	 * @return
	 */
	@Transactional
	public Response downloadSupplierInforExcelReport(String excelPath) {
		Response response = new Response();

		List<HeadQSupplier> suppliers = headQSupplierDaoImpl.getAll(true);

		//2. 准备excel 报表
		try {
		    HeadQSupplierInforTemplate rptTemplate = new HeadQSupplierInforTemplate(suppliers, excelPath);
			HSSFWorkbook wb = rptTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}
	

	
	/**
	 * 下在客户往来账目成excel

	 * @return
	 */
	public Response downloadCustAcctFlow(String excelPath, java.util.Date startDate, java.util.Date endDate, int custId,String tagGroups) {
		Response response = new Response();

		response = financeService.searchAcctFlow(startDate, endDate, Common_util.checkAllRecordValue(custId),tagGroups, false);
		
		Map<String, List> data = (Map)response.getReturnValue();
		List<ChainAcctFlowReportItem> items = (List<ChainAcctFlowReportItem>)data.get("rows");
		
		HeadQCust cust = headQCustDaoImpl.get(custId, true);
		//2. 准备excel 报表
		try {
		    HeadQCustAcctFlowTemplate rptTemplate = new HeadQCustAcctFlowTemplate(items, excelPath, cust, startDate, endDate);
		    HSSFWorkbook wb = rptTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}

	/**
	 * 准备 acct flow report的ui
	 * @param uiBean
	 */
	public void prepareAcctFlowReportUI(HeadQReportFormBean formBean,HeadQReportUIBean uiBean) {
		List<Year> years = yearDaoImpl.getAll(true);
		List<Quarter> quarters = quarterDaoImpl.getAll(true);
		
		uiBean.setQuarters(quarters);
		uiBean.setYears(years);
		
		formBean.setStartDate(Common_util.getToday());
		
	}


	/**
	 * 
	 * @param path
	 * @param startDate
	 * @param endDate
	 * @param year_ID
	 * @param quarter_ID
	 * @return
	 */
	@Transactional
	public Response downloadCustAcctFlowReport(String path, Date startDate, Date endDate, int yearId,int quarterId) {
		Response response = new Response();

		List<HeadQCustAcctFlowReportItem> items = new ArrayList<HeadQCustAcctFlowReportItem>();
		
		Year year = yearDaoImpl.get(yearId, true);
		Quarter quarter = quarterDaoImpl.get(quarterId, true);
		String curretnYearQuarter = year.getYear() + " " + quarter.getQuarter_Name();
		
		//1. 准备客户名单
		Set<Integer> chainStoreClientIds = chainStoreDaoImpl.getAllClientIds();
		chainStoreClientIds.remove(SystemParm.getTestClientId());
		
		//2. 准备日期
		java.util.Date startDate1 = Common_util.formStartDate(startDate);
		java.util.Date endDate1 = Common_util.formEndDate(endDate);
		
		//3.获取上期欠款
		Map<Integer, Double> lastPeriodAcctBalance = headQAcctFlowDaoImpl.getAccumulateAcctFlowBefore(chainStoreClientIds, startDate1);
		
		//4.获取当期期末欠款
		Map<Integer, Double> currentPeriodAcctBalance = headQAcctFlowDaoImpl.getAccumulateAcctFlowBefore(chainStoreClientIds, endDate1);
		
		//5.获取本期付款
		DetachedCriteria financeCriteria = DetachedCriteria.forClass(FinanceBill.class);
		
		financeCriteria.add(Restrictions.in("cust.id", chainStoreClientIds));
		financeCriteria.add(Restrictions.eq("status", FinanceBill.STATUS_COMPLETE));
		financeCriteria.add(Restrictions.eq("type", FinanceBill.FINANCE_INCOME_HQ));
		financeCriteria.add(Restrictions.between("billDate", startDate, endDate));
		
		ProjectionList projList = Projections.projectionList();
		projList.add(Projections.groupProperty("cust.id"));
		projList.add(Projections.sum("invoiceTotal"));
		financeCriteria.setProjection(projList);
		
		List<Object> result = financeBillImpl.getByCriteriaProjection(financeCriteria, false);
		Map<Integer, Double> stockMap = new HashMap<Integer, Double>();

		for (Object object : result)
		  if (object != null){
			Object[] recordResult = (Object[])object;
			int clientId = Common_util.getInt(recordResult[0]);
			double amount =  Common_util.getDouble(recordResult[1]);
			stockMap.put(clientId, amount);
		  } 
		
		//6. 获取本期发生 这段时间的销售金额
		DetachedCriteria inventoryCriteria = DetachedCriteria.forClass(InventoryOrder.class);
		
		inventoryCriteria.add(Restrictions.in("cust.id", chainStoreClientIds));
		inventoryCriteria.add(Restrictions.between("order_EndTime", startDate1, endDate1));
		inventoryCriteria.add(Restrictions.eq("order_Status", InventoryOrder.STATUS_ACCOUNT_COMPLETE));
		
		ProjectionList inventoryProjList = Projections.projectionList();
		inventoryProjList.add(Projections.groupProperty("cust.id"));
		inventoryProjList.add(Projections.groupProperty("order_type"));
		inventoryProjList.add(Projections.sum("totalWholePrice"));
		inventoryCriteria.setProjection(inventoryProjList);
		
		List<Object> inventoryResult = inventoryOrderDAOImpl.getByCriteriaProjection(inventoryCriteria, false);
		Map<Integer, Double> inventoryMap = new HashMap<Integer, Double>();

		for (Object object : inventoryResult)
		  if (object != null){
			Object[] recordResult = (Object[])object;
			int clientId = Common_util.getInt(recordResult[0]);
			int type = Common_util.getInt(recordResult[1]);
			double amount =  Common_util.getDouble(recordResult[2]);
			
			Double amountInMap = inventoryMap.get(clientId);
			if (amountInMap == null){
				amountInMap = new Double(0);
			}
			
			switch (type) {
				case InventoryOrder.TYPE_SALES_ORDER_W:
					amountInMap += amount;
					break;
				case InventoryOrder.TYPE_SALES_RETURN_ORDER_W:
					amountInMap -= amount;
					break;
				default:
					break;
			}
			
			inventoryMap.put(clientId, amountInMap);
		  } 
		
		//7. 获取当前季度的采购
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);

		String criteria = "SELECT SUM(wholeSalePrice * quantity),  p.order.cust.id, p.order.order_type FROM InventoryOrderProduct p WHERE p.order.order_Status = ?  AND p.productBarcode.product.year.year_ID = " + yearId + " AND p.productBarcode.product.quarter.quarter_ID = " + quarterId + " GROUP BY p.order.cust.id, p.order.order_type";
		List<Object> values = inventoryOrderProductDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
		
		Map<Integer, Double> inventoryOrderProductMap = new HashMap<Integer, Double>();
		if (values != null){
			for (Object record : values ){
				Object[] records = (Object[])record;

				double amount = Common_util.getDouble(records[0]);
				int clientId = Common_util.getInt(records[1]);
				int type = Common_util.getInt(records[2]);
				
				Double amountInMap = inventoryMap.get(clientId);
				if (amountInMap == null){
					amountInMap = new Double(0);
				}
				
				switch (type) {
					case InventoryOrder.TYPE_SALES_ORDER_W:
						amountInMap += amount;
						break;
					case InventoryOrder.TYPE_SALES_RETURN_ORDER_W:
						amountInMap -= amount;
						break;
					default:
						break;
				}
				
				inventoryOrderProductMap.put(clientId, amountInMap);
			}
		}

		//8.获取数据
		List<ChainStore> chainStores = chainStoreDaoImpl.getAllChainStoreList();
		for (ChainStore store : chainStores){
			int custId = store.getClient_id();
			
			if (custId == SystemParm.getTestClientId())
				continue;
			
			HeadQCustAcctFlowReportItem item = new HeadQCustAcctFlowReportItem(lastPeriodAcctBalance.get(custId), inventoryMap.get(custId), currentPeriodAcctBalance.get(custId), stockMap.get(custId), inventoryOrderProductMap.get(custId));
			item.setChainStore(store);
			
			items.add(item);
		}
		
		//2. 准备excel 报表
		try {
		    HeadQCustAcctFlowReportTemplate rptTemplate = new HeadQCustAcctFlowReportTemplate(items, path, startDate, endDate, curretnYearQuarter);
		    HSSFWorkbook wb = rptTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}

	public void prepareHQExpenseReportUI(HeadQReportFormBean formBean) {
		formBean.setStartDate(Common_util.getToday());
		formBean.setEndDate(Common_util.getToday());
	}
	
	/**
	 * 
	 * @param path
	 * @param startDate
	 * @param endDate
	 * @param year_ID
	 * @param quarter_ID
	 * @return
	 */
	@Transactional
	public Response downloadHeadQExpenseReport(String path, Date startDate, Date endDate) {
		Response response = new Response();
 
		//1.1 获取总信息
		List<Object> value_sale = new ArrayList<Object>();
		value_sale.add(startDate);
		value_sale.add(endDate);
		value_sale.add(Expense.statusE.NORMAL.getValue());
		
		String hql = "SELECT feeType, sum(amount)  FROM Expense WHERE expenseDate BETWEEN ? AND ? AND status =? AND entity IS null GROUP BY feeType";
		
		List<Object> values = expenseDaoImpl.executeHQLSelect(hql, value_sale.toArray(), null, true);
		
		HeadQExpenseRptElesVO totalItem = new HeadQExpenseRptElesVO("", 0,  HeadQSalesStatisticReportItemVO.STATE_CLOSED);
		
		if (values != null){
			for (Object record : values ){
				Object[] records = (Object[])record;
				int feeType = Common_util.getInt(records[0]);
				double amount = Common_util.getDouble(records[1]);

				totalItem.putValue(feeType, amount);
			}
		}
		
		totalItem.setParentExpenseTypeName("所有费用汇总");
		
		//1.2 获取汇总信息
		hql = "SELECT ExpenseHqType.parentId, feeType, sum(amount)  FROM Expense WHERE expenseDate BETWEEN ? AND ? AND status =? AND entity IS null  GROUP BY ExpenseHqType.parentId,feeType";
		
		values = expenseDaoImpl.executeHQLSelect(hql, value_sale.toArray(), null, true);
		
		Map<Integer, HeadQExpenseRptElesVO> sumMap = new HashMap<Integer, HeadQExpenseRptElesVO>();
		
		if (values != null){
			for (Object record : values ){
				Object[] records = (Object[])record;
				int expenseParentId = Common_util.getInt(records[0]);
				int feeType = Common_util.getInt(records[1]);
				double amount = Common_util.getDouble(records[2]);

				HeadQExpenseRptElesVO rootItem = sumMap.get(expenseParentId);
				if (rootItem == null){
					rootItem = new HeadQExpenseRptElesVO("", 1,  HeadQSalesStatisticReportItemVO.STATE_CLOSED);
					ExpenseHqType type = expenseHqTypeImpl.get(expenseParentId, true);
					rootItem.setName(type.getName());
					rootItem.setExpenseTypeParentId(expenseParentId);
				}
					
				rootItem.putValue(feeType, amount);
				sumMap.put(expenseParentId, rootItem);
			}
		}
		
		List<HeadQExpenseRptElesVO> sumItems = new ArrayList<HeadQExpenseRptElesVO>();
		for (HeadQExpenseRptElesVO rootItem : sumMap.values())
			sumItems.add(rootItem);
		
		Collections.sort(sumItems, new HeadQExpenseReportSort());
		
		
		//1.3获取子目录

		hql = "SELECT ExpenseHqType.parentId, ExpenseHqType.id, feeType, sum(amount)  FROM Expense WHERE expenseDate BETWEEN ? AND ? AND status =? AND entity IS null  GROUP BY ExpenseHqType.id,feeType";
		
		values = expenseDaoImpl.executeHQLSelect(hql, value_sale.toArray(), null, true);
		
		Map<Integer, HeadQExpenseRptElesVO> resultMap = new HashMap<Integer, HeadQExpenseRptElesVO>();
		
		if (values != null){
			String name = "";
			for (Object record : values ){
				Object[] records = (Object[])record;
				int parentExpenseHqTypeId = Common_util.getInt(records[0]);
				int ExpenseHqTypeId = Common_util.getInt(records[1]);
				int feeType = Common_util.getInt(records[2]);
				double amount = Common_util.getDouble(records[3]);

				HeadQExpenseRptElesVO rootItem = resultMap.get(ExpenseHqTypeId);
				if (rootItem == null){
					ExpenseHqType type = expenseHqTypeImpl.get(ExpenseHqTypeId, true);
					name = type.getName();				
					rootItem = new HeadQExpenseRptElesVO(name, 2,  HeadQSalesStatisticReportItemVO.STATE_OPEN);

					rootItem.setExpenseTypeId(ExpenseHqTypeId);
					rootItem.setExpenseTypeParentId(parentExpenseHqTypeId);
				}
					
				rootItem.putValue(feeType, amount);
				
				resultMap.put(ExpenseHqTypeId, rootItem);
			}
		}
		
		Map<Integer, List<HeadQExpenseRptElesVO>> eleMap = new HashMap<Integer, List<HeadQExpenseRptElesVO>>();
		for (HeadQExpenseRptElesVO rootItem : resultMap.values()){
			List<HeadQExpenseRptElesVO> eles = eleMap.get(rootItem.getExpenseTypeParentId());
		    
			if (eles == null)
				eles = new ArrayList<HeadQExpenseRptElesVO>();
			
			eles.add(rootItem);
			eleMap.put(rootItem.getExpenseTypeParentId(), eles);
		}
		
        
		
		//1.2 准备报表细节
		List<HeadQExpenseRptElesVO> reportItems = new ArrayList<HeadQExpenseRptElesVO>();
		int parentExpenseHqTypeId  = 0;
		for (HeadQExpenseRptElesVO item : sumItems){
			parentExpenseHqTypeId = item.getExpenseTypeParentId();
			
			List<HeadQExpenseRptElesVO> eles = eleMap.get(parentExpenseHqTypeId);

            Collections.sort(eles, new HeadQExpenseReportSort());
            eles.get(0).setParentExpenseTypeName(expenseHqTypeImpl.get(parentExpenseHqTypeId, true).getName());
            
            reportItems.addAll(eles);
            
            String name = item.getName();
            item.setName(name + " 汇总");
            reportItems.add(item);
		}
		
		reportItems.add(totalItem);
		
		//2. 准备excel 报表
		try {
		    HeadQExpenseReportTemplate rptTemplate = new HeadQExpenseReportTemplate(reportItems, path, startDate, endDate);
		    HSSFWorkbook wb = rptTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}

	/**
	 * 下载供应商账户流水报表
	 * @param string
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public Response downloadSupplierAcctFlowReport(String path, Date startDate, Date endDate) {
		Response response = new Response();

		List<HeadQSupplierAcctFlowReportItem> items = new ArrayList<HeadQSupplierAcctFlowReportItem>();

		
		//1. 准备供应商名单
		
		Set<Integer> supplierIds = headQSupplierDaoImpl.getAllSupplierIds();
		
		//2. 准备日期
		java.util.Date startDate1 = Common_util.formStartDate(startDate);
		java.util.Date endDate1 = Common_util.formEndDate(endDate);
		
		//3.获取上期欠款
		Map<Integer, Double> lastPeriodAcctBalance = supplierAcctFlowDaoImpl.getAccumulateAcctFlowBefore(supplierIds, startDate1);
		
		//4.获取当期期末欠款
		Map<Integer, Double> currentPeriodAcctBalance = supplierAcctFlowDaoImpl.getAccumulateAcctFlowBefore(supplierIds, endDate1);
		
		//5.获取本期付款
		DetachedCriteria financeCriteria = DetachedCriteria.forClass(FinanceBillSupplier.class);
		
		financeCriteria.add(Restrictions.in("supplier.id", supplierIds));
		financeCriteria.add(Restrictions.eq("status", FinanceBillSupplier.STATUS_COMPLETE));
		financeCriteria.add(Restrictions.eq("type", FinanceBillSupplier.FINANCE_PAID_HQ));
		financeCriteria.add(Restrictions.between("billDate", startDate, endDate));
		
		ProjectionList projList = Projections.projectionList();
		projList.add(Projections.groupProperty("supplier.id"));
		projList.add(Projections.sum("invoiceTotal"));
		financeCriteria.setProjection(projList);
		
		List<Object> result = financeBillImpl.getByCriteriaProjection(financeCriteria, false);
		Map<Integer, Double> stockMap = new HashMap<Integer, Double>();

		for (Object object : result)
		  if (object != null){
			Object[] recordResult = (Object[])object;
			int clientId = Common_util.getInt(recordResult[0]);
			double amount =  Common_util.getDouble(recordResult[1]);
			stockMap.put(clientId, amount);
		  } 
		
		//6. 获取本期发生 这段时间的销售金额
		DetachedCriteria inventoryCriteria = DetachedCriteria.forClass(PurchaseOrder.class);
		
		inventoryCriteria.add(Restrictions.in("supplier.id", supplierIds));
		inventoryCriteria.add(Restrictions.between("lastUpdateTime", startDate1, endDate1));
		inventoryCriteria.add(Restrictions.eq("status", PurchaseOrder.STATUS_COMPLETE));
		
		ProjectionList inventoryProjList = Projections.projectionList();
		inventoryProjList.add(Projections.groupProperty("supplier.id"));
		inventoryProjList.add(Projections.groupProperty("type"));
		inventoryProjList.add(Projections.sum("totalRecCost"));
		inventoryProjList.add(Projections.sum("totalQuantity"));
		inventoryCriteria.setProjection(inventoryProjList);
		
		List<Object> inventoryResult = purchaseOrderDaoImpl.getByCriteriaProjection(inventoryCriteria, false);
		Map<Integer, Double> purchaseAmtMap = new HashMap<Integer, Double>();
		Map<Integer, Double> returnAmtMap = new HashMap<Integer, Double>();
		Map<Integer, Integer> purchaseQMap = new HashMap<Integer, Integer>();
		Map<Integer, Integer> returnQMap = new HashMap<Integer, Integer>();

		for (Object object : inventoryResult)
		  if (object != null){
			Object[] recordResult = (Object[])object;
			int supplierId = Common_util.getInt(recordResult[0]);
			int type = Common_util.getInt(recordResult[1]);
			double amount =  Common_util.getDouble(recordResult[2]);
			int quantity = Common_util.getInt(recordResult[3]);
			
			switch (type) {
				case PurchaseOrder.TYPE_PURCHASE:
					
					Double purchaseAmt = purchaseAmtMap.get(supplierId);
					if (purchaseAmt == null){
						purchaseAmt = new Double(0);
					}
					purchaseAmt += amount;
					purchaseAmtMap.put(supplierId, purchaseAmt);
					
					Integer purchaseQ = purchaseQMap.get(supplierId);
					if (purchaseQ == null){
						purchaseQ = new Integer(0);
					}
					purchaseQ += quantity;
					purchaseQMap.put(supplierId, purchaseQ);
					
					break;
				case PurchaseOrder.TYPE_RETURN:
					Double returnAmt = returnAmtMap.get(supplierId);
					if (returnAmt == null){
						returnAmt = new Double(0);
					}
					
					returnAmt += amount;
					returnAmtMap.put(supplierId, returnAmt);
					
					Integer returnQ = returnQMap.get(supplierId);
					if (returnQ == null){
						returnQ = new Integer(0);
					}
					returnQ += quantity;
					returnQMap.put(supplierId, returnQ);
					break;
				default:
					break;
			}	

		  } 
		//7. 获取本期付款
		
		


		//8.获取数据
		List<HeadQSupplier> suppliers = headQSupplierDaoImpl.getAllSuppliers();
		for (HeadQSupplier supplier : suppliers){
			int supplierId = supplier.getId();

			HeadQSupplierAcctFlowReportItem item = new HeadQSupplierAcctFlowReportItem(lastPeriodAcctBalance.get(supplierId), stockMap.get(supplierId), currentPeriodAcctBalance.get(supplierId), purchaseQMap.get(supplierId), purchaseAmtMap.get(supplierId), returnQMap.get(supplierId), returnAmtMap.get(supplierId));		
			item.setSupplier(supplier);
			items.add(item);
		}
		
		//2. 准备excel 报表
		try {
		    HeadQSupplierAcctFlowReportTemplate rptTemplate = new HeadQSupplierAcctFlowReportTemplate(items, path, startDate, endDate);
		    HSSFWorkbook wb = rptTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}


	/**
	 * 单位销售统计
	 * 
	 * @param formBean
	 * @param page
	 * @param rowPerPage
	 * @param sort
	 * @param order
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Response getUnitSalesReportInfo(HeadQReportFormBean formBean,UserInfor userInfor) {
		Response response = new Response();
		Map data = new HashMap<String, Object>();
		List<Integer> pbIds = new ArrayList<Integer>(); // 符合条件的货品

		List<HeadQUnitSalesReportVO> list = new ArrayList<HeadQUnitSalesReportVO>();
		List<HeadQUnitSalesReportVO> footers = new ArrayList<HeadQUnitSalesReportVO>();
		HeadQUnitSalesReportVO footer = new HeadQUnitSalesReportVO();

		//1.获取符合条件的货品id
		pbIds = inventoryOrderProductDAOImpl.getProductIds(formBean);
		//2.获取客户
		List<Integer> cIds = getPurchaseCustIds(formBean) ;	
		//3.根据客户和货品 查询单位销售统计信息		
		List<InventoryOrderDTO> result = inventoryOrderDAOImpl.selectUnitSalesByClientId(cIds, pbIds,formBean);
		if (result.size() != 0) {
			Map<Integer,String> orderIdsMap = selectOrderIdsMap(result);
			
			Map<Integer, HeadQUnitSalesReportVO> map = new HashMap<Integer, HeadQUnitSalesReportVO>();
			
			for (InventoryOrderDTO dto : result) {			

				HeadQUnitSalesReportVO vo = map.get(dto.getClientId());			
				if (vo == null) {	
					vo = new HeadQUnitSalesReportVO();
					vo.setClientId(dto.getClientId());
					HeadQCust cust = headQCustDaoImpl.get(dto.getClientId(), true);
					vo.setClientName(cust.getName());
				}
				
				dressHqUntiSalesReportVO(dto, vo);
				vo.countReturnRate();
				map.put(dto.getClientId(), vo);
			}
			//装入list
			if (map.size() !=0) {
				for(Map.Entry<Integer, HeadQUnitSalesReportVO> entry : map.entrySet()) {
					list.add(entry.getValue());
				}
			}
			//整理list
			arrangeList(list,userInfor,orderIdsMap);			
			Collections.sort(list);
						
			if (list.size() !=0) {
				int sumQuantity = 0;
				int sumQuantity_R = 0;
				double sumRetailPrice = 0;
				double sumRetailPrice_R = 0;
				double sumWholePrice = 0;
				double sumWholePrice_R = 0;	
				int sumDmQuantiry = 0;		
				double sumDmNetSalesAmount =0;					
				int sumMdQuantiry =0;	
				double sumMdNetSalesAmount =0;
				for (HeadQUnitSalesReportVO reportVO : list) {
					sumQuantity +=reportVO.getTotalQuantity();
					sumQuantity_R +=reportVO.getTotalQuantityR();
					sumRetailPrice += reportVO.getTotalRecCost();
					sumRetailPrice_R +=reportVO.getTotalRecCostR();
					sumWholePrice +=reportVO.getTotalWholePrice();
					sumWholePrice_R +=reportVO.getTotalWholePriceR();
					sumDmQuantiry +=reportVO.getSumDmQuantity();
					sumDmNetSalesAmount +=reportVO.getSumDmNetSalesAmount();
					sumMdQuantiry +=reportVO.getSumMdQuantity();
					sumMdNetSalesAmount +=reportVO.getSumMdNetSalesAmount();					
				}
				footer = new HeadQUnitSalesReportVO(0,"合计","",sumQuantity,sumWholePrice,sumRetailPrice,sumQuantity_R,sumRetailPrice_R,sumWholePrice_R,sumDmQuantiry,sumDmNetSalesAmount,sumMdQuantiry,sumMdNetSalesAmount,null);
				footer.countReturnRate();
			}
		}
		
		footers.add(footer);
		data.put("footer", footers);
		data.put("rows", list);
		response.setReturnValue(data);

		return response;
	}
	
	/**
	 * 整理list
	 * @param list
	 * @param userInfor
	 * @param map
	 * @return
	 */
	private List<HeadQUnitSalesReportVO> arrangeList(List<HeadQUnitSalesReportVO> list,UserInfor userInfor,Map<Integer,String> map){
		
		if (list.size() !=0) {
			for (HeadQUnitSalesReportVO vo : list) {
				//查看成本权限
				if (!loginUserUtils.hasPermissionOnCost(userInfor)) {
					vo.setTotalRecCost(0);
					vo.setTotalRecCostR(0);
				}
				if (map.get(vo.getClientId()) != null) {
					vo.setOrderIds(map.get(vo.getClientId()));
				}				
				vo.setSumDmQuantity(vo.getDmQuantity() - vo.getDmQuantityR());
				vo.setSumDmNetSalesAmount(vo.getDmNetSalesAmount() - vo.getDmNetSalesAmountR());
				vo.setSumMdQuantity(vo.getMdQuantiry()-vo.getMdQuantiryR());
				vo.setSumMdNetSalesAmount(vo.getMdNetSalesAmount()-vo.getMdNetSalesAmountR());
				vo.setSumQuantity(vo.getTotalQuantity()-vo.getTotalQuantityR());
				vo.setSumWholePrice(vo.getTotalWholePrice()-vo.getTotalWholePriceR());
			}
		}
		return list;
	}
	
	
	
	/**
	 * 查询客户往来信息
	 * 
	 * @param formBean
	 * @param page
	 * @param rowPerPage
	 * @param sort
	 * @param order
	 * @return
	 * @throws ParseException 
	 */
	@SuppressWarnings("unchecked")
	public Response getDealiingsAnalysisReportInfo(HeadQReportFormBean formBean,UserInfor userInfor) throws ParseException {
		Response response = new Response();
		Map data = new HashMap<String, Object>();
		List<HeadQDealingsReportVO> result = new ArrayList<HeadQDealingsReportVO>();
		List<HeadQDealingsReportVO> footers = new ArrayList<HeadQDealingsReportVO>();
		List<HeadQCust> custs = new ArrayList<HeadQCust>();
		
		//1.客户集合
		List<Integer> cIds = getCustIds(formBean);	
		custs = headQCustDaoImpl.selectCustsByIds(cIds);
		
		// 2.赋值
		if (custs.size() !=0) {	
			//上期欠款
			result = chainAcctFlowDaoImpl.setCtpDebt(custs,formBean.getStartDate());
			//上期预存金
			result = chainFinanceTraceImpl.setCtpPrestore(custs,formBean.getStartDate(),result);
			//期中信息赋值
			result = financeBillImpl.setMidtermInfo(custs,formBean,result);
			//期中销售信息赋值
			result = inventoryOrderDAOImpl.setSaleInfo(cIds,formBean,result);
			//期末信息
			for (HeadQDealingsReportVO vo : result) {
				vo.setEndDebt(Common_util.roundDouble(vo.getCtpDebt() - vo.getMidtermPayment() + vo.getMidtermSale() - vo.getMidtermReturn(), 2));		
				vo.setEndPrestore(Common_util.roundDouble(vo.getCtpPrestore() + vo.getMidtermRecharge() - vo.getMidtermConsumption(), 2));
			}
			//当前信息			
			result = headQCustDaoImpl.selectNowInfo(custs,result);			
		}
		
		//3.合计
		HeadQDealingsReportVO footer = addFooters(result);
		footers.add(footer);
		data.put("footer", footers);
		data.put("rows", result);
		response.setReturnValue(data);

		return response;
	}
	
	/**
	 * 获取客户ids
	 * @param formBean
	 * @return
	 */
	private List<Integer> getCustIds(HeadQReportFormBean formBean) {
		List<Integer> cIds = new ArrayList<>();
		if(formBean.getCust().getId() != 0) {
			cIds.add(formBean.getCust().getId());
		}else {					
			if (formBean.getTagGroups() != null && formBean.getTagGroups().length() !=0) {
				cIds = headQCustDaoImpl.getTagGroupsCusts(formBean.getTagGroups());
			}else {									
				cIds = financeBillImpl.getClientIds(formBean);				
			}
		}
		return cIds;
	}
	
	/**
	 * 获取客户ids
	 * @param formBean
	 * @return
	 */
	private List<Integer> getPurchaseCustIds(HeadQReportFormBean formBean) {
		List<Integer> cIds = new ArrayList<>();
		if(formBean.getCust().getId() != 0) {
			cIds.add(formBean.getCust().getId());
		}else {					
			if (formBean.getTagGroups() != null && formBean.getTagGroups().length() !=0) {
				cIds = headQCustDaoImpl.getTagGroupsCusts(formBean.getTagGroups());
			}else {									
				cIds = inventoryOrderDAOImpl.getClientIds(formBean);				
			}
		}
		return cIds;
	}
	
	/**
	 * 添加页脚合计
	 * @param result
	 * @return
	 */
	private HeadQDealingsReportVO addFooters(List<HeadQDealingsReportVO> result) {
		HeadQDealingsReportVO footer = new HeadQDealingsReportVO();
		double ctpDebt =0;
		double ctpPrestore =0;
		double midtermPayment =0;
		double midtermConsumption =0;
		double midtermRecharge =0;
		double endDebt =0;
		double endPrestore =0;
		for (HeadQDealingsReportVO vo : result) {
			 ctpDebt += vo.getCtpDebt();
			 ctpPrestore += vo.getCtpPrestore();
			 midtermPayment += vo.getMidtermPayment();
			 midtermConsumption += vo.getMidtermConsumption();
			 midtermRecharge += vo.getMidtermRecharge();
			 endDebt += vo.getEndDebt();
			 endPrestore += vo.getEndPrestore();
		}
		footer.setClientName("合计");
		footer.setCtpDebt(Common_util.roundDouble(ctpDebt, 2));
		footer.setCtpPrestore(Common_util.roundDouble(ctpPrestore, 2));
		footer.setMidtermPayment(Common_util.roundDouble(midtermPayment, 2));
		footer.setMidtermConsumption(Common_util.roundDouble(midtermConsumption, 2));
		footer.setMidtermRecharge(Common_util.roundDouble(midtermRecharge, 2));
		footer.setEndDebt(Common_util.roundDouble(endDebt, 2));
		footer.setEndPrestore(Common_util.roundDouble(endPrestore, 2));
		return footer;
	}
	
	/**
	 * 拼装客户单据号
	 * key: 客户ID
	 * value : orderIds(1,2,3,4)
	 * @param result
	 * @return
	 */
	private HashMap<Integer, String> selectOrderIdsMap(List<InventoryOrderDTO> result) {
		HashMap<Integer, String> map = new HashMap<Integer, String>();
		if (result.size() !=0) {
			for (InventoryOrderDTO dto : result) {
				if (map.get(dto.getClientId())== null) {
					map.put(dto.getClientId(), Integer.toString(dto.getOrderId()));
				}else {
					String orderIds = map.get(dto.getClientId());
					map.put(dto.getClientId(), orderIds + "," + Integer.toString(dto.getOrderId()));
				}				
			}
		}		
		return map;
	}
	
	/**
	 * 封装vo
	 * @param dto
	 * @param vo
	 * @return
	 */
	private void  dressHqUntiSalesReportVO(InventoryOrderDTO dto,HeadQUnitSalesReportVO vo) {
		int isDaimai = dto.getIsDaimai();
		int isMaiduan = dto.getIsMaiduan();
		int orderType = dto.getOrderType();			
		//批发销售
		if (orderType == InventoryOrder.TYPE_SALES_ORDER_W && isDaimai == 0 && isMaiduan ==0) {
			vo.setTotalQuantity(vo.getTotalQuantity() + dto.getTotalQuantity());
			vo.setTotalRecCost(vo.getTotalRecCost() + dto.getTotalRecCost());
			vo.setTotalWholePrice(vo.getTotalWholePrice() + dto.getTotalWholePrice());
		}
		//批发退货
		if (orderType == InventoryOrder.TYPE_SALES_RETURN_ORDER_W && isDaimai == 0 && isMaiduan ==0) {
			vo.setTotalQuantityR(vo.getTotalQuantityR() + dto.getTotalQuantity());
			vo.setTotalRecCostR(vo.getTotalRecCostR() + dto.getTotalRecCost());
			vo.setTotalWholePriceR(vo.getTotalWholePriceR() + dto.getTotalWholePrice());
		}						
		//批发销售 && 代卖
		if (orderType == InventoryOrder.TYPE_SALES_ORDER_W && isDaimai == 1) {
			vo.setDmQuantity(vo.getDmQuantity() + dto.getTotalQuantity());
			vo.setDmNetSalesAmount(vo.getDmNetSalesAmount() + dto.getTotalWholePrice());
		}
		//批发退货 && 代卖
		if (orderType == InventoryOrder.TYPE_SALES_RETURN_ORDER_W && isDaimai == 1) {
			vo.setDmQuantityR(vo.getDmQuantity() + dto.getTotalQuantity());
			vo.setDmNetSalesAmountR(vo.getDmNetSalesAmountR() + dto.getTotalWholePrice());
		}										
		//批发销售 && 买断
		if (orderType == InventoryOrder.TYPE_SALES_ORDER_W && isMaiduan == 1) {
			vo.setMdQuantiry(vo.getMdQuantiry() + dto.getTotalQuantity());
			vo.setMdNetSalesAmount(vo.getMdNetSalesAmount() + dto.getTotalWholePrice());
		}
		//批发退货 && 买断
		if (orderType == InventoryOrder.TYPE_SALES_RETURN_ORDER_W && isMaiduan == 1) {
			vo.setMdQuantiryR(vo.getMdQuantiryR() + dto.getTotalQuantity());
			vo.setMdNetSalesAmountR(vo.getMdNetSalesAmountR() + dto.getTotalWholePrice());
		}
	}
	
	

	/**
	 * 查询客户标签
	 * @return
	 */
	public List<TagsVO> getTags() {
		List<TagsVO> list = new ArrayList<TagsVO>();
		List<HeadQTag> tags = headQTagDaoImpl.getAll(true);
		if (tags.size() !=0) {
			for (HeadQTag headQTag : tags) {
				TagsVO tagsVO = new TagsVO();
				tagsVO.setId(headQTag.getId());
				tagsVO.setText(headQTag.getTagName());
				list.add(tagsVO);
			}
		}
		return list;		
	}

	@Autowired
	private HeadQTagGroupDaoImpl tagGroupDaoImpl;
	
	public void prepareUnitReportUI(HeadQReportFormBean formBean, HeadQReportUIBean uiBean) {	
		List<HeadQCust> custs = headQCustDaoImpl.selectCusts();
		List<Year> years = yearDaoImpl.selectYears();
		List<Quarter> quarters = quarterDaoImpl.getAll(true);
		List<Brand> brands = brandDaoImpl.getAll(true);
		List<BelongToVO> belongToVOs = new ArrayList<BelongToVO>();
		BelongToVO AllBelongToVO = new BelongToVO();
		AllBelongToVO.setId(Common_util.ALL_RECORD);
		AllBelongToVO.setBelongToName("全部");
		BelongToVO qxBelongToVO = new BelongToVO();
		qxBelongToVO.setId(Brand.BELONG_TO_QX);
		qxBelongToVO.setBelongToName(Brand.belongToMap.get(Brand.BELONG_TO_QX));
		BelongToVO xlcBelongToVO = new BelongToVO();
		xlcBelongToVO.setId(Brand.BELONG_TO_XLC);
		xlcBelongToVO.setBelongToName(Brand.belongToMap.get(Brand.BELONG_TO_XLC));
		belongToVOs.add(AllBelongToVO);
		belongToVOs.add(qxBelongToVO);
		belongToVOs.add(xlcBelongToVO);
		
		//获取分组
		List<Integer> groups = tagGroupDaoImpl.selectByGroup(HeadQTagGroup.GROUP_ONE);
		List<HeadQTag> tagLevelOne = new ArrayList<HeadQTag>();
		if (groups.size() >0 )
			tagLevelOne = headQTagDaoImpl.getLevelOne(groups.get(0));
		List<HeadQTag> tagLevelTwo = new ArrayList<HeadQTag>();
		if (groups.size() >1 )
			tagLevelTwo = headQTagDaoImpl.getLevelOne(groups.get(1));
		List<HeadQTag> tagLevelThree = new ArrayList<HeadQTag>();
		if (groups.size() >2 )
			tagLevelThree = headQTagDaoImpl.getLevelOne(groups.get(2));
		List<HeadQTag> tagLevelFour = new ArrayList<HeadQTag>();
		if (groups.size() >3 )
			tagLevelFour = headQTagDaoImpl.getLevelOne(groups.get(3));

		uiBean.setTagLevelOne(tagLevelOne);
		uiBean.setTagLevelTwo(tagLevelTwo);
		uiBean.setTagLevelThree(tagLevelThree);
		uiBean.setTagLevelFour(tagLevelFour);
		uiBean.setCusts(custs);
		uiBean.setYears(years);
		uiBean.setQuarters(quarters);
		uiBean.setBrands(brands);
		uiBean.setBelongToVOs(belongToVOs);
	}
	

	public void getUnitSalesReportDetails(HeadQReportFormBean formBean) {
		List<HeadqUnitReportDetailsVO> list = new ArrayList<HeadqUnitReportDetailsVO>();
		List<String> orderIds = new ArrayList<String>();
		if (formBean.getOrderIds() != null && formBean.getOrderIds() != "") {
			orderIds = Arrays.asList(formBean.getOrderIds().split(","));
			for (String id : orderIds) {
				InventoryOrder order = inventoryOrderDAOImpl.get(Integer.valueOf(id), true);
				HeadqUnitReportDetailsVO vo = new HeadqUnitReportDetailsVO();
				vo.setOrderId(Integer.valueOf(id));
				vo.setClientName(order.getClient_name());
				vo.setOrderType(InventoryOrder.getTypeRetailer(order.getOrder_type()));
				vo.setStartTime(DateUtils.parseDateToStr(order.getOrder_StartTime()));
				vo.setCompleteTime(DateUtils.parseDateToStr(order.getOrder_ComplTime()));
				vo.setImportTimes(order.getImportTimes());
				vo.setTotalQ(order.getTotalQuantity());
				vo.setTotalWholeSales(order.getTotalWholePrice());
				vo.setIsDaimai(inventoryOrderDAOImpl.getDaimaiS(order));
				vo.setIsMaiduan(inventoryOrderDAOImpl.getMaiduanS(order));
				vo.setIsTransfer(inventoryOrderDAOImpl.getTransferS(order));
				vo.setComment(order.getComment());
				vo.setComment2(order.getComment2());
				list.add(vo);
			}
		}
		formBean.setUnitReportDetailsVOs(list);		
	}

	public Response generateHeadUnitExcel(String path, HeadQReportFormBean formBean,UserInfor userInfor) {
		Response response = new Response();
		try {
			
			Map data = new HashMap<String, Object>();
			data = (Map) getUnitSalesReportInfo(formBean,userInfor).getReturnValue();
						
			List<HeadQUnitSalesReportVO> list = (List<HeadQUnitSalesReportVO>) data.get("rows");
			HeadqUnitReportTemplate template = new HeadqUnitReportTemplate(path,list);
			
			HSSFWorkbook wb = template.process();
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);

			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (Exception e) {
			loggerLocal.error(e);
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}
	
	public Response generateDealingsExcel(String path, HeadQReportFormBean formBean,UserInfor userInfor) {
		Response response = new Response();
		try {
			
			Map data = new HashMap<String, Object>();
			data = (Map) getDealiingsAnalysisReportInfo(formBean,userInfor).getReturnValue();
						
			List<HeadQDealingsReportVO> list = (List<HeadQDealingsReportVO>) data.get("rows");
			HeadqDealingsReportTemplate template = new HeadqDealingsReportTemplate(path,list,formBean);
			
			HSSFWorkbook wb = template.process();
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);

			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (Exception e) {
			loggerLocal.error(e);
			e.printStackTrace();
			response.setFail(e.getMessage());
		}
		return response;
	}
	

	
	/**
	 * 总部商品销售统计报表平面数据 - （暂时不用）
	 * @param formBean
	 * @param page
	 * @param rowPerPage
	 * @param sort
	 * @param sortOrder
	 * @return
	 */
	public Response getSalesStatisticReptElesTwo(HeadQReportFormBean formBean,Integer page, Integer rowPerPage, String sort, String sortOrder) {
		Response response = new Response();
		Map<String,Object> data = new HashMap<String, Object>();
		List<HeadQSalesStatisticReportItemVO> list = new ArrayList<HeadQSalesStatisticReportItemVO>();
		List<HeadQSalesStatisticReportItemVO> footers = new ArrayList<HeadQSalesStatisticReportItemVO>();
		
		//总条数
		int total = inventoryOrderDAOImpl.selectSalesTotal(formBean);
		//销售信息
		List<SalesStatisticsDTO> sales = inventoryOrderDAOImpl.selectSales(formBean,page,rowPerPage,sort,sortOrder);
		
		list.clear();
		//销售退货信息
		List<Integer> ids = new ArrayList<Integer>();
		if (sales.size() !=0) {
			for (SalesStatisticsDTO dto : sales) {
				Color color = new Color();

				HeadQSalesStatisticReportItemVO vo = new HeadQSalesStatisticReportItemVO();
				vo.putValue(dto.getTotal(), InventoryOrder.TYPE_SALES_ORDER_W, dto.getSales(), dto.getCost());
				vo.setBarcode(dto.getBarcode());
				vo.setProductCode(dto.getProductCode());
				if (dto.getColorId() !=0) {
					 color = colorDaoImpl.get(dto.getColorId(), true);
					vo.setColorName(color.getName());
				}
				ids.add(dto.getPbId());
				list.add(vo);
			}
		}
		ids.clear();	
		Map<Integer, SalesStatisticsDTO> map = inventoryOrderDAOImpl.selectReturnSales(formBean.getStartDate(),formBean.getEndDate(),ids);
		
		for (HeadQSalesStatisticReportItemVO vo : list) {
			if (map.get(vo.getPbId())!= null) {
				vo.setReturnQ(map.get(vo.getPbId()).getTotal());
				vo.setReturnCost(map.get(vo.getPbId()).getCost());
				vo.setReturnPrice(map.get(vo.getPbId()).getSales());
			}
			vo.reCalculate();
		}
				
		//页脚合计部分	
		HeadQSalesStatisticReportItemVO footer = inventoryOrderDAOImpl.selectSalesFooters(formBean);
		footers.add(footer);
		data.put("footer", footers);
		data.put("rows", list);
		data.put("total", total);
		response.setReturnValue(data);
		response.setReturnCode(Response.SUCCESS);
		
		return response;
	}

	public void prepareSalesReportUI(HeadQReportFormBean formBean, HeadQReportUIBean uiBean) {
		
		List<HeadQCust> custs = new ArrayList<HeadQCust>();
		HeadQCust allCust = new HeadQCust();
		allCust.setId(-1);
		allCust.setName("所有客户");
		custs.add(allCust);
		List<HeadQCust> custs2 = headQCustDaoImpl.selectCusts();
		for (HeadQCust headQCust : custs2) {
			custs.add(headQCust);
		}
		formBean.getCust().setId(Common_util.ALL_RECORD);
		
		formBean.getSearchStartTime().setHours(0);

		List<BelongToVO> belongToVOs = brandDaoImpl.getBelongTos(2);

		uiBean.setCusts(custs);
		uiBean.setBelongToVOs(belongToVOs);
	}
	

	public void preparePurchaseReportUI(HeadQReportFormBean formBean, HeadQReportUIBean uiBean) {
		List<BelongToVO> belongToVOs = brandDaoImpl.getBelongTos(2);

		
		//List<HeadQTag> headQTags = headQTagDaoImpl.getAllSupplierTags();
		uiBean.setBelongToVOs(belongToVOs);
	}

	
	/**
	 * 总部商品销售统计树信息
	 * @param parentId
	 * @param startDate
	 * @param endDate
	 * @param custId
	 * @param code
	 * @param yearId
	 * @param quarterId
	 * @param brandId
	 * @param categoryLevelId
	 * @param categoryId
	 * @param reportType
	 * @return
	 */
	public Response getSalesStatisticReptEles(int parentId, Date startDate, Date endDate, int custId,String code,int belongTo, int yearId,
			int quarterId, int brandId,  int categoryLevelId,int categoryId, int reportType) {
		Response response = new Response();
		
		List<Object> value_sale = new ArrayList<Object>();
		
		value_sale.add(Common_util.formStartDate(startDate));
		value_sale.add(Common_util.formEndDate(endDate));
		value_sale.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE); 
		
		List<HqSalesStatisticReportItemVO> reportItems = new ArrayList<HqSalesStatisticReportItemVO>();
		String name = headQCustDaoImpl.getTemCust(custId).getName();
		
		String whereClause = "";
		//客户信息
		if (custId != Common_util.ALL_RECORD){
			whereClause += " AND csp.order.client_id = " + custId;
		}	
			
		if (StringUtils.isNotBlank(code) && !code.equals(", ")){
			//根据条码查询条码id
			String barcode = code.substring(0, 12); // 此处在树枝点击时，会有两个条码，需截取
			ProductBarcode pb = productBarcodeDaoImpl.getByBarcode(barcode);
			if (pb != null) {
				whereClause += " AND csp.productBarcode.id = " + pb.getId();
			}			
		}	
		
		//货品归属
		whereClause += " AND csp.productBarcode.product.brand.belong_To = ?";		
		value_sale.add(belongTo); 
				
		if (parentId == 0){
			//@2. 根节点
			String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.order.order_type FROM InventoryOrderProduct csp WHERE  csp.order.order_EndTime BETWEEN ? AND ? AND csp.order.order_Status = ?"+ whereClause  + " GROUP BY csp.order.order_type";

			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
			HqSalesStatisticReportItemVO rootItem = new HqSalesStatisticReportItemVO(name, 1, custId, yearId,
					quarterId, brandId, categoryLevelId, categoryId, 0, 
					HqSalesStatisticReportItemVO.STATE_CLOSED);		
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double sales = Common_util.getDouble(records[1]);
					double cost = Common_util.getDouble(records[2]);
					int type = Common_util.getInt(records[3]);
					
					rootItem.putValue(quantity, type, sales, cost);
				}
				rootItem.reCalculate();
			}
			
			reportItems.add(rootItem);
			Collections.sort(reportItems, new HqStatisticReportItemVOSorter());
		} else if (yearId == 0){
			//@2. 展开所有年份的库存信息
			String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.product.year.year_ID,  csp.order.order_type FROM InventoryOrderProduct csp WHERE  csp.order.order_EndTime BETWEEN ? AND ?  AND csp.order.order_Status = ?"+ whereClause  +  " GROUP BY csp.productBarcode.product.year.year_ID, csp.order.order_type";
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
			if (values != null){
				Map<Integer, HqSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HqSalesStatisticReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double sales = Common_util.getDouble(records[1]);
					double cost = Common_util.getDouble(records[2]);					
					int yearIdDB = Common_util.getInt(records[3]);
					int type = Common_util.getInt(records[4]);
					
					HqSalesStatisticReportItemVO levelFour = dataMap.get(yearIdDB);
					if (levelFour != null){
						levelFour.putValue(quantity, type, sales, cost);
					} else {
						Year year = yearDaoImpl.get(yearIdDB, true);
						
						name = year.getYear() + "年";
						
						levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearIdDB, quarterId,
								brandId, categoryLevelId, categoryId, 0,
								HqSalesStatisticReportItemVO.STATE_CLOSED);
						levelFour.putValue(quantity, type, sales, cost);
					}
					
					dataMap.put(yearIdDB, levelFour);
				}
				
				List<Integer> yearIds = new ArrayList<Integer>(dataMap.keySet());
				Collections.sort(yearIds);
				
				//1. 把基本对象放入
				for (Integer id : yearIds){
					HqSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
					levelFourItem.reCalculate();
					reportItems.add(levelFourItem);
				}	
				Collections.sort(reportItems, new HqStatisticReportItemVOSorter());
			}						
		}else if (quarterId == 0){
			//@2. 展开所有季的库存信息
			String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.product.quarter.quarter_ID, csp.order.order_type FROM InventoryOrderProduct csp WHERE  csp.order.order_EndTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = " + yearId + whereClause  +  " GROUP BY csp.productBarcode.product.quarter.quarter_ID, csp.order.order_type";
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
			if (values != null){
				Map<Integer, HqSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HqSalesStatisticReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double sales = Common_util.getDouble(records[1]);
					double cost = Common_util.getDouble(records[2]);				
					int quarterIdDB = Common_util.getInt(records[3]);
					int type = Common_util.getInt(records[4]);
					
					HqSalesStatisticReportItemVO levelFour = dataMap.get(quarterIdDB);
					if (levelFour != null){
						levelFour.putValue(quantity, type, sales, cost);
					} else {
						Year year = yearDaoImpl.get(yearId, true);
						Quarter quarter = quarterDaoImpl.get(quarterIdDB, true);
						name = year.getYear() + "年" + quarter.getQuarter_Name();
						
						levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearId, quarterIdDB,
								brandId, categoryLevelId, categoryId, 0,
								HqSalesStatisticReportItemVO.STATE_CLOSED);
						levelFour.putValue(quantity, type, sales, cost);
					}
					
					dataMap.put(quarterIdDB, levelFour);
				}
				
				List<Integer> quarterIds = new ArrayList<Integer>(dataMap.keySet());
				Collections.sort(quarterIds);
				
				//1. 把基本对象放入
				for (Integer id : quarterIds){
					HqSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
					levelFourItem.reCalculate();
					reportItems.add(levelFourItem);
				}	
				Collections.sort(reportItems, new HqStatisticReportItemVOSorter());
			}
		}else {
			if (reportType == 1) {
				if (categoryLevelId == 0) {
					// @2. 展开所有产品线的库存信息
					String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.product.categoryLevel1.category_ID,  csp.order.order_type FROM InventoryOrderProduct csp WHERE csp.order.order_EndTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = "
							+ yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause + " GROUP BY csp.productBarcode.product.categoryLevel1.category_ID, csp.order.order_type";
					List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null,true);

					if (values != null) {
						Map<Integer, HqSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HqSalesStatisticReportItemVO>();

						for (Object record : values) {
							Object[] records = (Object[]) record;
							int quantity = Common_util.getInt(records[0]);
							double sales = Common_util.getDouble(records[1]);
							double cost = Common_util.getDouble(records[2]);
							int categoryLevel = Common_util.getInt(records[3]);
							int type = Common_util.getInt(records[4]);
											 
							HqSalesStatisticReportItemVO levelFour = dataMap.get(categoryLevel);
							if (levelFour != null) {
								levelFour.putValue(quantity, type, sales, cost);
							} else {
								Category category = categoryDaoImpl.get(categoryLevel, true);

								name = category.getCategory_Name();

								levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearId,
										quarterId, brandId, categoryLevel, categoryId, 0, 
										HqSalesStatisticReportItemVO.STATE_CLOSED);
								levelFour.putValue(quantity, type, sales, cost);

							}
							dataMap.put(categoryLevel, levelFour);
						}

						List<Integer> brandIds = new ArrayList<Integer>(dataMap.keySet());

						// 1. 把基本对象放入
						for (Integer id : brandIds) {
							HqSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
							levelFourItem.reCalculate();
							
							reportItems.add(levelFourItem);
						}
						Collections.sort(reportItems, new HqStatisticReportItemVOSorter());
					}
				} else if (categoryId == 0) {
					// @2. 展开所有某一产品线下所有货品的库存信息
					String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.product.category.category_ID,  csp.order.order_type FROM InventoryOrderProduct csp WHERE csp.order.order_EndTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = "
							+ yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause
							+ " AND csp.productBarcode.product.categoryLevel1.category_ID = " + categoryLevelId
						    + " GROUP BY csp.productBarcode.product.category.category_ID, csp.order.order_type";
					List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null,
							true);

					if (values != null) {
						Map<Integer, HqSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HqSalesStatisticReportItemVO>();
						for (Object record : values) {
							Object[] records = (Object[]) record;
							int quantity = Common_util.getInt(records[0]);
							double sales = Common_util.getDouble(records[1]);
							double cost = Common_util.getDouble(records[2]);
							int categoryIdDB = Common_util.getInt(records[3]);
							int type = Common_util.getInt(records[4]);

							HqSalesStatisticReportItemVO levelFour = dataMap.get(categoryIdDB);
							if (levelFour != null) {
								levelFour.putValue(quantity, type, sales, cost);
							} else {
								Category category = categoryDaoImpl.get(categoryIdDB, true);

								name = category.getCategory_Name();

								levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearId,
										quarterId, brandId, categoryLevelId, categoryIdDB, 0,
										HqSalesStatisticReportItemVO.STATE_CLOSED);
								levelFour.putValue(quantity, type, sales, cost);
							}						
							dataMap.put(categoryIdDB, levelFour);
						}

						List<Integer> brandIds = new ArrayList<Integer>(dataMap.keySet());

						// 1. 把基本对象放入
						for (Integer id : brandIds) {
							HqSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
							levelFourItem.reCalculate();

							reportItems.add(levelFourItem);
						}
						Collections.sort(reportItems,new HqStatisticReportItemVOSorter());
					}
					
				} else {

					// @2. 展开具体某一产品线下具体的某一货品类别的库存信息
					String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.id,  csp.order.order_type FROM InventoryOrderProduct csp WHERE csp.order.order_EndTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = "
							+ yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause
							+ " AND csp.productBarcode.product.categoryLevel1.category_ID = " + categoryLevelId
							+ " AND csp.productBarcode.product.category.category_ID = " + categoryId
							+ " GROUP BY csp.productBarcode.id, csp.order.order_type";
					List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null,
							true);

					if (values != null) {
						Map<Integer, HqSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HqSalesStatisticReportItemVO>();
						for (Object record : values) {
							Object[] records = (Object[]) record;
							int quantity = Common_util.getInt(records[0]);
							double sales = Common_util.getDouble(records[1]);
							double cost = Common_util.getDouble(records[2]);
							int pbId = Common_util.getInt(records[3]);
							int type = Common_util.getInt(records[4]);
							ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
							HqSalesStatisticReportItemVO levelFour = dataMap.get(pbId);
							if (levelFour != null) {
								levelFour.putValue(quantity, type, sales, cost);
							} else {
								
								String barcode = pb.getBarcode();
								Color color = pb.getColor();
								String colorName = "";
								if (color != null)
									colorName = color.getName();

								Product product = pb.getProduct();

								Brand brand = brandDaoImpl.get(product.getBrand().getBrand_ID(), true);							

								name = Common_util.cutProductCode(pb.getProduct().getProductCode()) + colorName + " "
										 + brand.getBrand_Name();

								boolean isChain = false;
								if (pb.getChainStore() != null && pb.getChainStore().getChain_id() != 0)
									isChain = true;

								levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearId,
										quarterId, brandId, categoryLevelId, categoryId, pbId,
										HqSalesStatisticReportItemVO.STATE_OPEN);
								levelFour.putValue(quantity, type, sales, cost);
								levelFour.setIsChain(isChain);
								levelFour.setBarcode(barcode);
								
								String isExist = productBarcodePicDaoImpl.getURLPathByBarcode(barcode, ProductBarcodePic.TYPE_PING_MIAN_PIC);
								if (isExist != null) {
									levelFour.setImg("详情");
								}
							}
							dataMap.put(pbId, levelFour);
						}

						List<Integer> pbIds = new ArrayList<Integer>(dataMap.keySet());

						// 1. 把基本对象放入
						for (Integer id : pbIds) {
							HqSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
							levelFourItem.reCalculate();

							reportItems.add(levelFourItem);
						}
						Collections.sort(reportItems,new HqStatisticReportItemVOSorter());
						}					
					}					
				} else if(reportType == 0) {
					if (brandId == 0){
						//@2. 展开所有品牌的库存信息
						String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.product.brand.brand_ID,  csp.order.order_type FROM InventoryOrderProduct csp WHERE  csp.order.order_EndTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = " + yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause  + " GROUP BY csp.productBarcode.product.brand.brand_ID, csp.order.order_type";
						List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
						
						if (values != null){
							Map<Integer, HqSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HqSalesStatisticReportItemVO>();
							for (Object record : values ){
								Object[] records = (Object[])record;
								int quantity = Common_util.getInt(records[0]);
								double sales = Common_util.getDouble(records[1]);
								double cost = Common_util.getDouble(records[2]);
								int brandIdDB = Common_util.getInt(records[3]);
								int type = Common_util.getInt(records[4]);
								
								HqSalesStatisticReportItemVO levelFour = dataMap.get(brandIdDB);
								if (levelFour != null){
									levelFour.putValue(quantity, type, sales, cost);
								} else {
									Brand brand = brandDaoImpl.get(brandIdDB, true);
									boolean isChain = false;
									if (brand.getChainStore() != null && brand.getChainStore().getChain_id() !=0)
										isChain = true;
									String pinyin = brand.getPinyin();
									if (!StringUtils.isEmpty(pinyin)){
										name = pinyin.substring(0, 1) + " ";
									}
									
									 name += brand.getBrand_Name();
								 
									 levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearId,
												quarterId, brandIdDB, categoryLevelId, categoryId, 0,
												HqSalesStatisticReportItemVO.STATE_CLOSED);							
									 levelFour.setIsChain(isChain);
									levelFour.putValue(quantity, type, sales, cost);
								}
								dataMap.put(brandIdDB, levelFour);
							}
							
							List<Integer> brandIds = new ArrayList<Integer>(dataMap.keySet());
							Collections.sort(brandIds);
							
							//1. 把基本对象放入
							for (Integer id : brandIds){
								HqSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
								levelFourItem.reCalculate();

								reportItems.add(levelFourItem);
							}	
							Collections.sort(reportItems,new HqStatisticReportItemVOSorter());
						}
					} else if (brandId != 0) {
						//@2. 展开所有品牌的库存信息
						String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.id,  csp.order.order_type FROM InventoryOrderProduct csp WHERE  csp.order.order_EndTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = " + yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + " AND csp.productBarcode.product.brand.brand_ID = " + brandId + whereClause + " GROUP BY csp.productBarcode.id, csp.order.order_type";
						List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
						
						if (values != null){
							Map<Integer, HqSalesStatisticReportItemVO> dataMap = new HashMap<Integer, HqSalesStatisticReportItemVO>();
							String barcode = "";
							for (Object record : values ){
								Object[] records = (Object[])record;
								int quantity = Common_util.getInt(records[0]);
								double sales = Common_util.getDouble(records[1]);
								double cost = Common_util.getDouble(records[2]);								
								int pbId = Common_util.getInt(records[3]);
								int type = Common_util.getInt(records[4]);
								
								HqSalesStatisticReportItemVO levelFour = dataMap.get(pbId);
								ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
								if (levelFour != null){
									levelFour.putValue(quantity, type, sales, cost);
								} else {
									
								    barcode = pb.getBarcode();
									Color color = pb.getColor();
									String colorName = "";
									if (color != null)
										colorName = color.getName();

									Category categoryLevel = pb.getProduct().getCategoryLevel1();
									Category category = pb.getProduct().getCategory();
									name = Common_util.cutProductCode(pb.getProduct().getProductCode()) + colorName + " " + categoryLevel.getCategory_Name() + category.getCategory_Name();
									
									boolean isChain = false;
									if (pb.getChainStore() != null && pb.getChainStore().getChain_id() !=0)
										isChain = true;

									levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearId,
											quarterId, brandId, categoryLevelId, categoryId, pbId,
											HqSalesStatisticReportItemVO.STATE_OPEN);
									levelFour.putValue(quantity, type, sales, cost);
									levelFour.setIsChain(isChain);
									levelFour.setBarcode(barcode);
								}
								dataMap.put(pbId, levelFour);
							}
							
							List<Integer> pbIds = new ArrayList<Integer>(dataMap.keySet());
							Collections.sort(pbIds);
							
							//1. 把基本对象放入
							for (Integer id : pbIds){
								HqSalesStatisticReportItemVO levelFourItem = dataMap.get(id);
								levelFourItem.reCalculate();
								String isExist = productBarcodePicDaoImpl.getURLPathByBarcode(levelFourItem.getBarcode(), ProductBarcodePic.TYPE_PING_MIAN_PIC);
								if (isExist != null) {
									levelFourItem.setImg("详情");
								}	
								reportItems.add(levelFourItem);
							}	
							Collections.sort(reportItems,new HqStatisticReportItemVOSorter());
						}
					}
				}				
			}						
	    response.setReturnValue(reportItems);
	    return response;
	}

	public Response generateChainSalesStatisticExcelReport(int parentId, int custId, String barcode,Date startDate, Date endDate,int yearId, int quarterId, int brandId, int categoryLevelId, int categoryId, String path) {
	
		Response response = new Response();	
		
			List<Object> value_sale = new ArrayList<Object>();
			value_sale.add(Common_util.formStartDate(startDate));
			value_sale.add(Common_util.formEndDate(endDate));
			value_sale.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE); 
			
			//1. part one is to calculate the summary
			String whereClause = "";
			//客户信息
			if (custId != Common_util.ALL_RECORD){
				whereClause += " AND csp.order.client_id = " + custId;
			}
			//单条码
			if (StringUtils.isNotBlank(barcode)){
				//根据条码查询条码id			
				ProductBarcode pb = productBarcodeDaoImpl.getByBarcode(barcode);
				if (barcode != null) {
					whereClause += " AND csp.productBarcode.id = " + pb.getId();
				}			
			}	
						
			String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.id,  csp.order.order_type FROM InventoryOrderProduct csp WHERE  csp.order.order_StartTime BETWEEN ? AND ?  AND csp.order.order_Status = ?" ;
			
			if (yearId != 0){
				whereClause += " AND csp.productBarcode.product.year.year_ID = " + yearId;
			}
			
			if (quarterId != 0){
				whereClause += " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId;
			}
			
			if (brandId != 0){
				whereClause += " AND csp.productBarcode.product.brand.brand_ID = " + brandId;
			}
			
			
			criteria += whereClause ;
			String criteiraSummary = criteria + " GROUP BY csp.productBarcode.id, csp.order.order_type";
			
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteiraSummary, value_sale.toArray(), null, true);
			
			Map<String, HqSalesStatisticReportItemVO> dataMap = new HashMap<String, HqSalesStatisticReportItemVO>();
			
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					double sales = Common_util.getDouble(records[1]);
					double cost = Common_util.getDouble(records[2]);
					int pbId = Common_util.getInt(records[3]);
					int type = Common_util.getInt(records[4]);
					
					HqSalesStatisticReportItemVO levelFour = dataMap.get(String.valueOf(pbId));
					if (levelFour != null){
						levelFour.putValue(quantity, type, sales, cost);
					} else {
					    String name = "";
						ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
						
						boolean isChain = false;
						if (pb.getChainStore() != null && pb.getChainStore().getChain_id() !=0)
							isChain = true;

						levelFour = new HqSalesStatisticReportItemVO(name, parentId, custId, yearId, quarterId, brandId,
								categoryLevelId, categoryId, pbId, HqSalesStatisticReportItemVO.STATE_OPEN);					
						levelFour.putValue(quantity, type, sales, cost);
						levelFour.setIsChain(isChain);
					}
					
					dataMap.put(String.valueOf(pbId), levelFour);
				}
			}
				
			List<HqSalesStatisReportItem> reportItems = new ArrayList<HqSalesStatisReportItem>();
			HqSalesStatisReportItem totalItem = new HqSalesStatisReportItem();
			List<HqSalesStatisticReportItemVO> reportItemVOs = new ArrayList<HqSalesStatisticReportItemVO>(dataMap.values());
			

			HeadQCust cust = headQCustDaoImpl.getTemCust(custId);
			
			for (HqSalesStatisticReportItemVO vo : reportItemVOs){
	            int pbId = vo.getPbId();
	            ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
	            
	            vo.reCalculate();
	            
				HqSalesStatisReportItem reportItem = new HqSalesStatisReportItem(vo, cust, startDate, endDate, pb);
				totalItem.add(reportItem);
				reportItems.add(reportItem);
			}
					
			/**
			 * @3. 准备excel报表
			 */

			try {

				HqSalesStatisticsReportTemplate hqSalesStatisticsReportTemplate = new HqSalesStatisticsReportTemplate(reportItems,totalItem, cust, path, startDate, endDate);
				HSSFWorkbook wb = hqSalesStatisticsReportTemplate.process();
				
				ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
				
				response.setReturnValue(byteArrayInputStream);
				response.setReturnCode(Response.SUCCESS);
			} catch (IOException e){
				response.setReturnCode(Response.FAIL);
			}
			return response;
	}

	public Response generateChainSalesStatisticExcelReportDetail(int parentId, int custId,String barcode, Date startDate, Date endDate,int yearId, int quarterId, int brandId, int categoryLevelId, int categoryId, String path) {
		
	    Response response = new Response();	
		
		List<Object> value_sale = new ArrayList<Object>();		
		value_sale.add(Common_util.formStartDate(startDate));
		value_sale.add(Common_util.formEndDate(endDate));
		value_sale.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);
				
		//1. part one is to calculate the summary
		String whereClause = "";
		//客户信息
		if (custId != Common_util.ALL_RECORD){
			whereClause += " AND csp.order.client_id = " + custId;
		}	
		
		//单条码
		if (StringUtils.isNotBlank(barcode)){
			//根据条码查询条码id			
			ProductBarcode pb = productBarcodeDaoImpl.getByBarcode(barcode);
			if (barcode != null) {
				whereClause += " AND csp.productBarcode.id = " + pb.getId();
			}			
		}	
		
		String criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.id,  csp.order.order_type FROM InventoryOrderProduct csp WHERE  csp.order.order_StartTime BETWEEN ? AND ? AND csp.order.order_Status = ?" ;
		
		if (yearId != 0){
			whereClause += " AND csp.productBarcode.product.year.year_ID = " + yearId;
		}
		
		if (quarterId != 0){
			whereClause += " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId;
		}
		
		if (brandId != 0){
			whereClause += " AND csp.productBarcode.product.brand.brand_ID = " + brandId;
		}


		Map<String, HqSalesStatisticReportItemVO> dataMap = new HashMap<String, HqSalesStatisticReportItemVO>();


		List<HqSalesStatisReportItem> reportItemsDetail = new ArrayList<HqSalesStatisReportItem>();
		HqSalesStatisReportItem totalItem = new HqSalesStatisReportItem();
		
		HeadQCust cust = headQCustDaoImpl.getTemCust(custId);
		
		//2. part two is to calculate the detail elements
		criteria = "SELECT SUM(csp.quantity), SUM(csp.wholeSalePrice * csp.quantity), SUM(csp.recCost * csp.quantity), csp.productBarcode.id,  csp.order.order_type, csp.order.client_id, csp.order.order_StartTime,csp.order.order_ID,csp.order.comment2  FROM InventoryOrderProduct csp WHERE csp.order.order_StartTime BETWEEN ? AND ? AND csp.order.order_Status = ? ";
		criteria += whereClause ;
		String criteriaDetail = criteria + " GROUP BY csp.order.client_id, csp.order.order_StartTime, csp.productBarcode.id, csp.order.order_type";
		
		List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteriaDetail, value_sale.toArray(), null, true);

		dataMap = new HashMap<String, HqSalesStatisticReportItemVO>();
		if (values != null){
			for (Object record : values ){
				Object[] records = (Object[])record;
				int quantity = Common_util.getInt(records[0]);
				double sales = Common_util.getDouble(records[1]);
				double cost = Common_util.getDouble(records[2]);
				int pbId = Common_util.getInt(records[3]);
				int type = Common_util.getInt(records[4]);
				int custIdDB = Common_util.getInt(records[5]); //客户id
				Date date = new java.sql.Date(Common_util.getTimestampDate(records[6]).getTime());
				int orderId = Common_util.getInt(records[7]);
			    String comment = Common_util.getString(records[8]);
				
				String key = formatKey(custIdDB, date, pbId);
				
				//System.out.println(key);
				
				HqSalesStatisticReportItemVO levelFour = dataMap.get(key);
				if (levelFour != null){
					levelFour.putValue(quantity, type, sales, cost);
				} else {
				    String name = "";
					ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
					
					if (pb == null)
						continue;
					
					boolean isChain = false;
					if (pb.getChainStore() != null && pb.getChainStore().getChain_id() !=0)
						isChain = true;

					levelFour = new HqSalesStatisticReportItemVO(name, parentId, custIdDB, yearId, quarterId,
							brandId, categoryLevelId, categoryId, pbId, 
							HqSalesStatisticReportItemVO.STATE_OPEN);
					levelFour.setDate(Common_util.dateFormat.format(date));
					levelFour.setOrderId(orderId);
					levelFour.setComment(comment);
					levelFour.putValue(quantity, type, sales, cost);
					levelFour.setIsChain(isChain);
				}
				
				dataMap.put(key, levelFour);
			}
		}
			
		List<HqSalesStatisticReportItemVO> reportItemVOs2 = new ArrayList<HqSalesStatisticReportItemVO>(dataMap.values());

		
		for (HqSalesStatisticReportItemVO vo : reportItemVOs2){
            int pbId = vo.getPbId();
            ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
            
            vo.reCalculate();
            
            HeadQCust custTwo = headQCustDaoImpl.get(vo.getCustId(), true);
            
            HqSalesStatisReportItem reportItem = new HqSalesStatisReportItem(vo, custTwo,  startDate, endDate, pb);

			reportItemsDetail.add(reportItem);
		}
		
		Collections.sort(reportItemsDetail, new HqSalesStatisticDetailReportItemVOSorter());
		
		/**
		 * @3. 准备excel报表
		 */

		try {
			
			HqSalesStatisticsReportDetailTemplate hqSalesStatisticsReportTemplate = new HqSalesStatisticsReportDetailTemplate(reportItemsDetail,totalItem, cust, path,   startDate, endDate);
			XSSFWorkbook wb = hqSalesStatisticsReportTemplate.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcel2007ToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (Exception e){
			response.setReturnCode(Response.FAIL);
		}
		return response;
	}
	
	private String formatKey(int custId, Date date, Integer pbId){
		return custId+"@"+date.toString()+"@"+pbId;
	}
	
	
	 class HqSalesStatisticDetailReportItemVOSorter implements
		Comparator<HqSalesStatisReportItem> {

		@Override
		public int compare(HqSalesStatisReportItem o1,
				HqSalesStatisReportItem o2) {
			String date1 = o1.getDate();
			String date2 = o2.getDate();
						
			String pc1 = o1.getProductBarcode().getProduct().getProductCode();
			String pc2 = o2.getProductBarcode().getProduct().getProductCode();
			
			
			if (!date1.equals(date2)){
				return date2.compareTo(date1);
			}  else {
				return pc1.compareTo(pc2);
		}			
	}
}


	 /**
	  * 追踪客户采购，退货记录历史信息
	  * @param formBean
	  * @return
	  */
	public Response selectSalesRecords(HeadQReportFormBean formBean) {
		Response response = new Response();
		Map data = new HashMap<String, List>();
		List<SalesRecordsVO> traceVOs = new ArrayList<SalesRecordsVO>();
		List<Object> value_sale = new ArrayList<Object>();
		
		value_sale.add(Common_util.getLastYearDate());
		value_sale.add(Common_util.getToday());
		String whereClause = "";
		//客户信息
		if (formBean.getCust().getId() != Common_util.ALL_RECORD){
			whereClause += " AND csp.order.client_id = " + formBean.getCust().getId();
		}	
			
		if (StringUtils.isNotBlank(formBean.getBarcode())){
			//根据条码查询条码id		
			ProductBarcode pb = productBarcodeDaoImpl.getByBarcode(formBean.getBarcode());
			if (pb != null) {
				whereClause += " AND csp.productBarcode.id = " + pb.getId();
			}			
		}	
		
			String criteria = "SELECT csp.order.client_name,csp.order.order_StartTime,csp.order.order_type,csp.quantity,csp.order.order_ID from InventoryOrderProduct csp where csp.order.order_StartTime BETWEEN ? AND ? " +whereClause  + " and csp.order.order_Status = "+InventoryOrder.STATUS_ACCOUNT_COMPLETE; 
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
			if (values != null){
				for (Object record : values ){
					SalesRecordsVO sRecordsVO = new SalesRecordsVO();
					Object[] records = (Object[])record;
					String clientName = Common_util.getString(records[0]);
					String date = Common_util.getString(records[1]);
					String type =InventoryOrder.getTypeWholeSaler( Common_util.getInt(records[2]));
					int quantity = Common_util.getInt(records[3]);
					int orderId = Common_util.getInt(records[4]);
					sRecordsVO.setClientName(clientName);
					sRecordsVO.setDate(date);
					sRecordsVO.setType(type);
					sRecordsVO.setQuantity(quantity);
					sRecordsVO.setOrderId(orderId);
					traceVOs.add(sRecordsVO);
				}
			}

		data.put("rows", traceVOs);
		response.setReturnValue(data);
		return response;
	}
	
	
	/**
	 * 总部商品销售统计树信息
	 * @param parentId
	 * @param startDate
	 * @param endDate
	 * @param custId
	 * @param code
	 * @param yearId
	 * @param quarterId
	 * @param brandId
	 * @param categoryLevelId
	 * @param categoryId
	 * @param reportType
	 * @return
	 */
	public Response getTabletStatistics(int parentId, java.util.Date startDate, java.util.Date endDate, int custId,String code,int belongTo, int yearId,
			int quarterId, int brandId,  int categoryLevelId,int categoryId, int reportType) {
		Response response = new Response();
		
		List<Object> value_sale = new ArrayList<Object>();
		
		value_sale.add(startDate);
		value_sale.add(endDate);
		value_sale.add(InventoryOrder.STATUS_PEIHUO_COMPLETE); 
		
		List<HqTabletReportItemVO> reportItems = new ArrayList<HqTabletReportItemVO>();
		String name = headQCustDaoImpl.getTemCust(custId).getName();
		
		String whereClause = "";
		//客户信息
		if (custId != Common_util.ALL_RECORD){
			whereClause += " AND csp.order.client_id = " + custId;
		}			
		
		//货品归属
		whereClause += " AND csp.productBarcode.product.brand.belong_To = ?";		
		value_sale.add(belongTo); 
				
		if (parentId == 0){
			//@2. 根节点
			String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo) FROM InventoryOrderProduct csp WHERE  csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ?"+ whereClause;

			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
			HqTabletReportItemVO rootItem = new HqTabletReportItemVO(name, 1, custId, yearId,
					quarterId, brandId, categoryLevelId, categoryId, 0, 
					HqTabletReportItemVO.STATE_CLOSED);		
			if (values != null){
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					int quantityPei = Common_util.getInt(records[1]);
					rootItem.putValue(quantity, quantityPei);
				}				
			}
			
			reportItems.add(rootItem);
			Collections.sort(reportItems, new HqStatisticReportItemVOSorter());
		} else if (yearId == 0){
			//@2. 展开所有年份的库存信息
			String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo), csp.productBarcode.product.year.year_ID  FROM InventoryOrderProduct csp WHERE  csp.order.order_PeiHuoComplTime BETWEEN ? AND ?  AND csp.order.order_Status = ?"+ whereClause  +" GROUP BY csp.productBarcode.product.year.year_ID";
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
			if (values != null){
				Map<Integer, HqTabletReportItemVO> dataMap = new HashMap<Integer, HqTabletReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					int quantityPei = Common_util.getInt(records[1]);				
					int yearIdDB = Common_util.getInt(records[2]);
					
					HqTabletReportItemVO levelFour = dataMap.get(yearIdDB);
					if (levelFour != null){
						levelFour.putValue(quantity,quantityPei);

					} else {
						Year year = yearDaoImpl.get(yearIdDB, true);
						
						name = year.getYear() + "年";
						
						levelFour = new HqTabletReportItemVO(name, parentId, custId, yearIdDB, quarterId,
								brandId, categoryLevelId, categoryId, 0,
								HqTabletReportItemVO.STATE_CLOSED);
						levelFour.putValue(quantity, quantityPei);
					}
					
					dataMap.put(yearIdDB, levelFour);
				}
				
				List<Integer> yearIds = new ArrayList<Integer>(dataMap.keySet());
				Collections.sort(yearIds);
				
				//1. 把基本对象放入
				for (Integer id : yearIds){
					HqTabletReportItemVO levelFourItem = dataMap.get(id);					
					reportItems.add(levelFourItem);
				}	
				Collections.sort(reportItems, new HqStatisticReportItemVOSorter());
			}						
		}else if (quarterId == 0){
			//@2. 展开所有季的库存信息
			String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo), csp.productBarcode.product.quarter.quarter_ID FROM InventoryOrderProduct csp WHERE  csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = " + yearId + whereClause  + " GROUP BY csp.productBarcode.product.quarter.quarter_ID";
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
			if (values != null){
				Map<Integer, HqTabletReportItemVO> dataMap = new HashMap<Integer, HqTabletReportItemVO>();
				for (Object record : values ){
					Object[] records = (Object[])record;
					int quantity = Common_util.getInt(records[0]);
					int quantityPei = Common_util.getInt(records[1]);					
					int quarterIdDB = Common_util.getInt(records[2]);
					
					HqTabletReportItemVO levelFour = dataMap.get(quarterIdDB);
					if (levelFour != null){
						levelFour.putValue(quantity,quantityPei);
					} else {
						Year year = yearDaoImpl.get(yearId, true);
						Quarter quarter = quarterDaoImpl.get(quarterIdDB, true);
						name = year.getYear() + "年" + quarter.getQuarter_Name();
						
						levelFour = new HqTabletReportItemVO(name, parentId, custId, yearId, quarterIdDB,
								brandId, categoryLevelId, categoryId, 0,
								HqSalesStatisticReportItemVO.STATE_CLOSED);
						levelFour.putValue(quantity, quantityPei);
					}
					
					dataMap.put(quarterIdDB, levelFour);
				}
				
				List<Integer> quarterIds = new ArrayList<Integer>(dataMap.keySet());
				Collections.sort(quarterIds);
				
				//1. 把基本对象放入
				for (Integer id : quarterIds){
					HqTabletReportItemVO levelFourItem = dataMap.get(id);
					reportItems.add(levelFourItem);
				}	
				Collections.sort(reportItems, new HqStatisticReportItemVOSorter());
			}
		}else {
			if (reportType == 1) {
				if (categoryLevelId == 0) {
					// @2. 展开所有产品线的库存信息
					String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo), csp.productBarcode.product.categoryLevel1.category_ID  FROM InventoryOrderProduct csp WHERE csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = "
							+ yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause +" GROUP BY csp.productBarcode.product.categoryLevel1.category_ID";
					List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null,true);

					if (values != null) {
						Map<Integer, HqTabletReportItemVO> dataMap = new HashMap<Integer, HqTabletReportItemVO>();

						for (Object record : values) {
							Object[] records = (Object[]) record;
							int quantity = Common_util.getInt(records[0]);
							int quantityPei = Common_util.getInt(records[1]);	
							int categoryLevel = Common_util.getInt(records[2]);
											 
							HqTabletReportItemVO levelFour = dataMap.get(categoryLevel);
							if (levelFour != null) {
								levelFour.putValue(quantity, quantityPei);
							} else {
								Category category = categoryDaoImpl.get(categoryLevel, true);

								name = category.getCategory_Name();

								levelFour = new HqTabletReportItemVO(name, parentId, custId, yearId,
										quarterId, brandId, categoryLevel, categoryId, 0, 
										HqSalesStatisticReportItemVO.STATE_CLOSED);
								levelFour.putValue(quantity, quantityPei);

							}
							dataMap.put(categoryLevel, levelFour);
						}

						List<Integer> brandIds = new ArrayList<Integer>(dataMap.keySet());

						// 1. 把基本对象放入
						for (Integer id : brandIds) {
							HqTabletReportItemVO levelFourItem = dataMap.get(id);							
							reportItems.add(levelFourItem);
						}
						Collections.sort(reportItems, new HqStatisticReportItemVOByQuantitySorter());
					}
				} else if (categoryId == 0) {
					// @2. 展开所有某一产品线下所有货品的库存信息
					String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo), csp.productBarcode.product.category.category_ID  FROM InventoryOrderProduct csp WHERE csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = "
							+ yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause
							+ " AND csp.productBarcode.product.categoryLevel1.category_ID = " + categoryLevelId
						    + " GROUP BY csp.productBarcode.product.category.category_ID";
					List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null,true);

					if (values != null) {
						Map<Integer, HqTabletReportItemVO> dataMap = new HashMap<Integer, HqTabletReportItemVO>();
						for (Object record : values) {
							Object[] records = (Object[]) record;
							int quantity = Common_util.getInt(records[0]);
							int quantityPei = Common_util.getInt(records[1]);	
							int categoryIdDB = Common_util.getInt(records[2]);

							HqTabletReportItemVO levelFour = dataMap.get(categoryIdDB);
							if (levelFour != null) {
								levelFour.putValue(quantity, quantityPei);
							} else {
								Category category = categoryDaoImpl.get(categoryIdDB, true);

								name = category.getCategory_Name();

								levelFour = new HqTabletReportItemVO(name, parentId, custId, yearId,
										quarterId, brandId, categoryLevelId, categoryIdDB, 0,
										HqSalesStatisticReportItemVO.STATE_CLOSED);
								levelFour.putValue(quantity, quantityPei);
							}						
							dataMap.put(categoryIdDB, levelFour);
						}

						List<Integer> brandIds = new ArrayList<Integer>(dataMap.keySet());

						// 1. 把基本对象放入
						for (Integer id : brandIds) {
							HqTabletReportItemVO levelFourItem = dataMap.get(id);
							reportItems.add(levelFourItem);
						}
						Collections.sort(reportItems,new HqStatisticReportItemVOByQuantitySorter());
					}
					
				} else {

					// @2. 展开具体某一产品线下具体的某一货品类别的库存信息
					String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo), csp.productBarcode.id FROM InventoryOrderProduct csp WHERE csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = "
							+ yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause
							+ " AND csp.productBarcode.product.categoryLevel1.category_ID = " + categoryLevelId
							+ " AND csp.productBarcode.product.category.category_ID = " + categoryId
							+ " GROUP BY csp.productBarcode.id";
					List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null,true);

					if (values != null) {
						Map<Integer, HqTabletReportItemVO> dataMap = new HashMap<Integer, HqTabletReportItemVO>();
						for (Object record : values) {
							Object[] records = (Object[]) record;
							int quantity = Common_util.getInt(records[0]);
							int quantityPei = Common_util.getInt(records[1]);	
							int pbId = Common_util.getInt(records[2]);

							ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
							HqTabletReportItemVO levelFour = dataMap.get(pbId);
							if (levelFour != null) {
								levelFour.putValue(quantity, quantityPei);
							} else {
								
								String barcode = pb.getBarcode();
								Color color = pb.getColor();
								String colorName = "";
								if (color != null)
									colorName = color.getName();

								Product product = pb.getProduct();

								Brand brand = brandDaoImpl.get(product.getBrand().getBrand_ID(), true);							

								name = Common_util.cutProductCode(pb.getProduct().getProductCode()) + colorName + " "
										 + brand.getBrand_Name();

//								boolean isChain = false;
//								if (pb.getChainStore() != null && pb.getChainStore().getChain_id() != 0)
//									isChain = true;

								levelFour = new HqTabletReportItemVO(name, parentId, custId, yearId,
										quarterId, brandId, categoryLevelId, categoryId, pbId,
										HqTabletReportItemVO.STATE_OPEN);
								levelFour.putValue(quantity, quantityPei);
//								levelFour.setIsChain(isChain);	
								levelFour.setBarcode(barcode);
								if (pb.getStatus() == ProductBarcode.STATUS_NOT_PRODUCE)
								   levelFour.setNotManuf(true);
								
								String isExist = productBarcodePicDaoImpl.getURLPathByBarcode(barcode, ProductBarcodePic.TYPE_PING_MIAN_PIC);
								if (isExist != null) {
									levelFour.setImg("详情");
								}
							}
							dataMap.put(pbId, levelFour);
						}

						Set<Integer> pbIds = new HashSet<Integer>(dataMap.keySet());
						
						Map<Integer, Integer> inventoryMap = headQInventoryStockDAOImpl.getProductsInventoryLevel(pbIds, Common_util.ALL_RECORD);

						// 1. 把基本对象放入
						for (Integer id : pbIds) {
							HqTabletReportItemVO levelFourItem = dataMap.get(id);
							
							Integer inventoryInteger = inventoryMap.get(id);
							if (inventoryInteger != null)
								levelFourItem.setInventory(inventoryInteger);
							
							reportItems.add(levelFourItem);
						}
						Collections.sort(reportItems,new HqStatisticReportItemVOByQuantitySorter());
						}					
					}					
				} else if(reportType == 0) {
					if (brandId == 0){
						//@2. 展开所有品牌的库存信息
						String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo), csp.productBarcode.product.brand.brand_ID FROM InventoryOrderProduct csp WHERE  csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = " + yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + whereClause  +" GROUP BY csp.productBarcode.product.brand.brand_ID";
						List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
												
						if (values != null){
							Map<Integer, HqTabletReportItemVO> dataMap = new HashMap<Integer, HqTabletReportItemVO>();
							for (Object record : values ){
								Object[] records = (Object[])record;
								int quantity = Common_util.getInt(records[0]);
								int quantityPei = Common_util.getInt(records[1]);	
								int brandIdDB = Common_util.getInt(records[2]);
								
								HqTabletReportItemVO levelFour = dataMap.get(brandIdDB);
								if (levelFour != null){
									levelFour.putValue(quantity, quantityPei);
								} else {
									Brand brand = brandDaoImpl.get(brandIdDB, true);
									boolean isChain = false;
									if (brand.getChainStore() != null && brand.getChainStore().getChain_id() !=0)
										isChain = true;
									String pinyin = brand.getPinyin();
									if (!StringUtils.isEmpty(pinyin)){
										name = pinyin.substring(0, 1) + " ";
									}
									
									 name += brand.getBrand_Name();
								 
									 levelFour = new HqTabletReportItemVO(name, parentId, custId, yearId,
												quarterId, brandIdDB, categoryLevelId, categoryId, 0,
												HqTabletReportItemVO.STATE_CLOSED);							
									 levelFour.setIsChain(isChain);
									levelFour.putValue(quantity, quantityPei);
								}
								dataMap.put(brandIdDB, levelFour);
							}
							
							List<Integer> brandIds = new ArrayList<Integer>(dataMap.keySet());
							Collections.sort(brandIds);
							
							//1. 把基本对象放入
							for (Integer id : brandIds){
								HqTabletReportItemVO levelFourItem = dataMap.get(id);
								reportItems.add(levelFourItem);
							}	
							Collections.sort(reportItems,new HqStatisticReportItemVOSorter());
						}
					} else if (brandId != 0) {
						//@2. 展开所有品牌的库存信息
						String criteria = "SELECT SUM(csp.quantity), SUM(csp.quantityPeihuo), csp.productBarcode.id   FROM InventoryOrderProduct csp WHERE  csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ? AND csp.productBarcode.product.year.year_ID = " + yearId + " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId + " AND csp.productBarcode.product.brand.brand_ID = " + brandId + whereClause + " GROUP BY csp.productBarcode.id";
						List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
						
						if (values != null){
							Map<Integer, HqTabletReportItemVO> dataMap = new HashMap<Integer, HqTabletReportItemVO>();
							String barcode = "";
							for (Object record : values ){
								Object[] records = (Object[])record;
								int quantity = Common_util.getInt(records[0]);
								int quantityPei = Common_util.getInt(records[1]);									
								int pbId = Common_util.getInt(records[2]);
								
								HqTabletReportItemVO levelFour = dataMap.get(pbId);
								ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
								if (levelFour != null){
									levelFour.putValue(quantity, quantityPei);
								} else {
									
								    barcode = pb.getBarcode();
									Color color = pb.getColor();
									String colorName = "";
									if (color != null)
										colorName = color.getName();

									Category categoryLevel = pb.getProduct().getCategoryLevel1();
									Category category = pb.getProduct().getCategory();
									name = Common_util.cutProductCode(pb.getProduct().getProductCode()) + colorName + " " + categoryLevel.getCategory_Name() + category.getCategory_Name();
									
//									boolean isChain = false;
//									if (pb.getChainStore() != null && pb.getChainStore().getChain_id() !=0)
//										isChain = true;

									levelFour = new HqTabletReportItemVO(name, parentId, custId, yearId,
											quarterId, brandId, categoryLevelId, categoryId, pbId,
											HqTabletReportItemVO.STATE_OPEN);
									levelFour.putValue(quantity, quantityPei);
//									levelFour.setIsChain(isChain);
									levelFour.setBarcode(barcode);
									if (pb.getStatus() == ProductBarcode.STATUS_NOT_PRODUCE)
										   levelFour.setNotManuf(true);
								}
								dataMap.put(pbId, levelFour);
							}
							
							Set<Integer> pbIds = new HashSet<Integer>(dataMap.keySet());
							Map<Integer, Integer> inventoryMap = headQInventoryStockDAOImpl.getProductsInventoryLevel(pbIds, Common_util.ALL_RECORD);

							
							//1. 把基本对象放入
							for (Integer id : pbIds){
								HqTabletReportItemVO levelFourItem = dataMap.get(id);

								Integer inventoryInteger = inventoryMap.get(id);
								if (inventoryInteger != null)
									levelFourItem.setInventory(inventoryInteger);
								
								String isExist = productBarcodePicDaoImpl.getURLPathByBarcode(levelFourItem.getBarcode(), ProductBarcodePic.TYPE_PING_MIAN_PIC);
								if (isExist != null) {
									levelFourItem.setImg("详情");
								}	
								reportItems.add(levelFourItem);
							}	
							Collections.sort(reportItems,new HqStatisticReportItemVOSorter());
						}
					}
				}				
			}						
	    response.setReturnValue(reportItems);
	    return response;
	}

	/**
	 * 导出平板配货统计报表
	 * @param parentId
	 * @param startDate
	 * @param endDate
	 * @param custId
	 * @param code
	 * @param belongTo
	 * @param yearId
	 * @param quarterId
	 * @param brandId
	 * @param categoryLevelId
	 * @param categoryId
	 * @param reportType
	 * @param path
	 * @return
	 */
	public Response downloadTabletExcelReport(int parentId, java.util.Date startDate, java.util.Date endDate, int custId,String code,int belongTo, int yearId,
			int quarterId, int brandId,  int categoryLevelId,int categoryId, int reportType, String path) {
		Response response = new Response();
		
		List<HqPeiHuoExpoertVO> reportItems = new ArrayList<HqPeiHuoExpoertVO>();
		
		List<Object> value_sale = new ArrayList<Object>();
		
		value_sale.add(startDate);
		value_sale.add(endDate);
		value_sale.add(InventoryOrder.STATUS_PEIHUO_COMPLETE);
		
		String whereClause = "";
		//客户信息
		if (custId != Common_util.ALL_RECORD){
			whereClause += " AND csp.order.client_id = " + custId;
		}					
		//货品归属
		whereClause += " AND csp.productBarcode.product.brand.belong_To = ?";		
		value_sale.add(belongTo); 
		
		String sql = "";
		if(reportType == 0) {
			
			if (yearId != Common_util.ALL_RECORD_NEW) {
				sql +="AND csp.productBarcode.product.year.year_ID = " + yearId  ;
			}
			if (quarterId != Common_util.ALL_RECORD_NEW) {
				sql+= " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId ;
			}
			if (brandId != Common_util.ALL_RECORD_NEW) {
				sql+= " AND csp.productBarcode.product.brand.brand_ID = " + brandId;
			}
			
			String criteria = "SELECT csp.order.order_StartTime,  csp.order.client_name, csp.quantity, csp.quantityPeihuo, csp.productBarcode.id,csp.order.order_PeiHuoComplTime   FROM InventoryOrderProduct csp WHERE  csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ?" + sql + whereClause;
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);
						
			if (values != null){
			
				for (Object record : values ){
					Object[] records = (Object[])record;
					Timestamp orderDateTime = Common_util.getTime(records[0]);
					String custName =Common_util.getString(records[1]);
					int quantity = Common_util.getInt(records[2]);
					int quantityPei = Common_util.getInt(records[3]);									
					int pbId = Common_util.getInt(records[4]);
					Timestamp peiHuoTime = Common_util.getTime(records[5]);
					
					HqPeiHuoExpoertVO vo = new HqPeiHuoExpoertVO();
					ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
					if (pb != null) {
						vo.setOrderDateTime(Common_util.dateFormat_f.format(orderDateTime));
						vo.setPeihuoDateTime(Common_util.dateFormat_f.format(peiHuoTime));
						vo.setCustName(custName);
						vo.setBarcode(pb.getBarcode());
						vo.setProductCode(pb.getProduct().getProductCode());
						vo.setQuantity(quantity);
						if (pb.getColor() != null)
						     vo.setColor(pb.getColor().getName());
						vo.setQuarter(pb.getProduct().getYear().getYear() + pb.getProduct().getQuarter().getQuarter_Name());
						vo.setCategoryLevel1(pb.getProduct().getCategoryLevel1().getCategory_Name());
						vo.setCategory(pb.getProduct().getCategory().getCategory_Name());
						vo.setQuantityPei(quantityPei);
						vo.setNoQuantityPei(vo.getQuantity()-vo.getQuantityPei());
						vo.setBrand(pb.getProduct().getBrand().getBrand_Name());
						reportItems.add(vo);
					}										
				}
			}			
		}else if (reportType == 1) {
			if (yearId != Common_util.ALL_RECORD_NEW) {
				sql +="AND csp.productBarcode.product.year.year_ID = " + yearId  ;
			}
			if (quarterId != Common_util.ALL_RECORD_NEW) {
				sql+= " AND csp.productBarcode.product.quarter.quarter_ID = " + quarterId ;
			}
			if (categoryLevelId != Common_util.ALL_RECORD_NEW) {
				sql+= " AND csp.productBarcode.product.categoryLevel1.category_ID = " + categoryLevelId;
			}
			if (categoryId != Common_util.ALL_RECORD_NEW) {
				sql+= " AND csp.productBarcode.product.category.category_ID = " + categoryId;
			}
			
			String criteria = "SELECT csp.order.order_StartTime,  csp.order.client_name, csp.quantity, csp.quantityPeihuo, csp.productBarcode.id,csp.order.order_PeiHuoComplTime  FROM InventoryOrderProduct csp WHERE csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_Status = ? "+ sql +  whereClause;
			List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale.toArray(), null, true);			
			
			if (values != null){

				for (Object record : values ){
					Object[] records = (Object[])record;
					Timestamp orderDateTime = Common_util.getTime(records[0]);
					String custName =Common_util.getString(records[1]);
					int quantity = Common_util.getInt(records[2]);
					int quantityPei = Common_util.getInt(records[3]);									
					int pbId = Common_util.getInt(records[4]);
					Timestamp peiHuoTime = Common_util.getTime(records[5]);
					
					HqPeiHuoExpoertVO vo = new HqPeiHuoExpoertVO();
					ProductBarcode pb = productBarcodeDaoImpl.get(pbId, true);
					if (pb != null) {
						vo.setOrderDateTime(Common_util.dateFormat_f.format(orderDateTime));
						vo.setPeihuoDateTime(Common_util.dateFormat_f.format(peiHuoTime));
						vo.setCustName(custName);
						vo.setBarcode(pb.getBarcode());
						vo.setProductCode(pb.getProduct().getProductCode());
						vo.setQuantity(quantity);
						vo.setColor(pb.getColor().getName());
						vo.setQuarter(pb.getProduct().getYear().getYear() + pb.getProduct().getQuarter().getQuarter_Name());
						vo.setCategoryLevel1(pb.getProduct().getCategoryLevel1().getCategory_Name());
						vo.setCategory(pb.getProduct().getCategory().getCategory_Name());
						vo.setQuantityPei(quantityPei);
						vo.setNoQuantityPei(vo.getQuantity()-vo.getQuantityPei());
						vo.setBrand(pb.getProduct().getBrand().getBrand_Name());
						reportItems.add(vo);
					}										
				}
			}
		}
					
		HeadQCust cust = headQCustDaoImpl.getTemCust(custId);

			
		/**
		 * @3. 准备excel报表
		 */

		try {

			HqTabletReportTemplate template = new HqTabletReportTemplate(reportItems, cust, path, startDate, endDate);
			HSSFWorkbook wb = template.process();
			
			ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
			
			response.setReturnValue(byteArrayInputStream);
			response.setReturnCode(Response.SUCCESS);
		} catch (IOException e){
			response.setReturnCode(Response.FAIL);
		}
		return response;
	}

	public Response generateHqSalesStatisticLevelReport(List<HqSalesStatisticReportItemVO> list, int custId, String path,
			Date startDate, Date endDate) throws IOException {					 
		Response response = new Response();		
		
		HeadQCust cust = new HeadQCust();
		if (custId ==Common_util.ALL_RECORD) {
			cust.setName("所有客户");
		}else {
			cust = headQCustDaoImpl.get(custId, true);
		}

		HqSalesStatisticsReportTemplate hqSalesStatisticsReportTemplate = new HqSalesStatisticsReportTemplate(list, cust, path, startDate, endDate);
		HSSFWorkbook wb = hqSalesStatisticsReportTemplate.processLevel();
		
		ByteArrayInputStream byteArrayInputStream = ExcelUtil.convertExcelToInputStream(wb);
		
		response.setReturnValue(byteArrayInputStream);
		response.setReturnCode(Response.SUCCESS);
	

		return response;
	}

	/**
	 * 获取客户选货配货的跟踪报表
	 * @param barcode
	 * @param searchStartTime
	 * @param searchEndTime
	 * @param reportType 1:选货信息 2：配货信息 3:未配信息
	 * @return
	 */
	public Response getPeiHuoTraceRecords(int clientId, String barcode, java.util.Date searchStartTime, java.util.Date searchEndTime,
			int reportType) {
		Response response = new Response();
		String whereClause = "";

		if (StringUtils.isNotBlank(barcode)){
			//根据条码查询条码id		
			ProductBarcode pb = productBarcodeDaoImpl.getByBarcode(barcode);
			if (pb != null) {
				List<SalesRecordsVO> traceVOs = new ArrayList<SalesRecordsVO>();
				
				whereClause += " AND csp.productBarcode.id = " + pb.getId();
				
				//客户信息
				if (clientId != Common_util.ALL_RECORD){
					whereClause += " AND csp.order.client_id = " + clientId;
				}	
				
				Object[] value_sale = {searchStartTime,searchEndTime};
				
				String criteria = "SELECT csp.order.client_name,csp.order.order_PeiHuoComplTime,csp.order.order_ID, csp.quantity, csp.quantityPeihuo,csp.order.client_id from InventoryOrderProduct csp where csp.order.order_PeiHuoComplTime BETWEEN ? AND ? AND csp.order.order_type=" + InventoryOrder.TYPE_SALES_ORDER_W +whereClause  + " and csp.order.order_Status = "+InventoryOrder.STATUS_PEIHUO_COMPLETE; 
				List<Object> values = inventoryOrderDAOImpl.executeHQLSelect(criteria, value_sale, null, true);
				if (values != null){
					for (Object record : values ){
						SalesRecordsVO sRecordsVO = new SalesRecordsVO();
						Object[] records = (Object[])record;
						String clientName = Common_util.getString(records[0]);
						String date = Common_util.getString(records[1]);
						int orderId = Common_util.getInt(records[2]);
						int quantity = Common_util.getInt(records[3]);
						int quantityPeihuo = Common_util.getInt(records[4]);
						int clientIdDB = Common_util.getInt(records[5]);
						sRecordsVO.setClientName(clientName);
						sRecordsVO.setDate(date);
						
						if (reportType == 1 || (reportType == 2 && quantityPeihuo > 0) || (reportType == 3 && (quantity - quantityPeihuo) > 0)) {
							//获取配货日期后的发货情况
							List<Integer> statusSet = new ArrayList<Integer>();
							statusSet.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);
							int deliveryQ = inventoryOrderDAOImpl.getInventoryProductQuantity(clientIdDB, pb.getId(), statusSet, date);
							sRecordsVO.setDeliveryQ(deliveryQ);
							
							sRecordsVO.setQuantity(quantity);
							sRecordsVO.setQuantityPeihuo(quantityPeihuo);
							sRecordsVO.setNoQuantity(quantity - quantityPeihuo);
							sRecordsVO.setOrderId(orderId);
							traceVOs.add(sRecordsVO);
						}
					}
				}

				
			    response.setReturnValue(traceVOs);
			} else {
				response.setFail("具体条码信息没有 : " + barcode);
			}
	
		} else 
			response.setFail("具体条码信息没有 : " + barcode);
		return response;
	}


}
